countif noncontiguous (Excel 2007)

Topic

New Reply

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…

Reply | Quote

Replies

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”)

Reply | Quote

You are simply amazing

Thanks

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

=SUM(COUNTIF(INDIRECT({“B5″,”C7″,”D2″}),”test”))

Regards
Bosco

Reply | Quote

That’s very clever but does it work with a defined named that refers to a non-contiguous range?

Reply | Quote