# Thread: countif noncontiguous (Excel 2007)

1. ## countif noncontiguous (Excel 2007)

Is it possible to have the COUNTIF function operate on a set of noncontiguous cells?

For example, I'd like to create a named range of cells (Named). The cells in the named range are noncontiguous (e.g. B5, C7, D2).

Then I'd like to create a formula that is "=COUNTIF(Named,"test")" so that it will count the number of cells in my named range that contain the word test.

Thanks...

2. ## Re: countif noncontiguous (Excel 2007)

COUNTIF doesn't work with non-contiguous ranges. There may be complicated array formula solutions, or you could create a custom function:

Function MyCountIf(oRange, aValue) As Long
Dim oCell As Range
For Each oCell In oRange.Cells
MyCountIf = MyCountIf - (oCell = aValue)
Next oCell
End Function

and use it like this:

=MyCountIf(Named, "Test")

If you store the function in your personal macro workbook Personal.xlsm, use

=Personal.xlsm!MyCountIf(Named, "Test")

3. ## Re: countif noncontiguous (Excel 2007)

You are simply amazing

Thanks

4. ## Re: countif noncontiguous (Excel 2007)

Hans,

I know your code works because I tried it, but I'm puzzled:
When oCell=aValue, ie when "Test"="Test" my logic tells me this is True which seems to me SHOULD return a 1 rather than a negative 1.
Hence, I would have (without testing) instinctively written
MyCountif = MyCountif + (oCell = aValue)

Can you explain the error in my thinking?

Thanks

5. ## Re: countif noncontiguous (Excel 2007)

In Visual Basic, True and False are stored as integers, where True = -1 and False = 0.

In a computer, the number -1 is stored as a series of bits that are all "on", and the number 0 as a series of bits that are all "off", hence the use of -1 for True and 0 for False.

(In theory, it would be more efficient to store True and False as single bits, where True = 1 and False = 0. However, the CPUs used in personal computers are optimized for handling data in chunks of 8, 16, 32 and 64 bits, handling single bits is relatively slow. Hence the decision to store True and False as integers)

6. ## Re: countif noncontiguous (Excel 2007)

COUNTIF can work with non-contiguous ranges, and it's not even array entered !

=SUM(COUNTIF(INDIRECT({"B5","C7","D2"}),"test"))

Regards
Bosco

7. ## Re: countif noncontiguous (Excel 2007)

That's very clever <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> but does it work with a defined named that refers to a non-contiguous range?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•