non-contiguous ranges in CountIf

[Bradley Dawson]

I was trying to use COUNTIF to see if a value in a cell was also in a range
of cells, but the range is non-contiguous. For good reason, the range is
sorted in a certain way. The commas between the contiguous ranges seemed
the throw the formula off. Enclosing the range in paranthesis didn't help,
neither did naming the range and using the name. There is little
documentation about this in Help. Any Ideas?

 

[Peo Sjoblom]

Countif does not work for non-contiguous ranges, if your values are numeric
there are some workarounds. Please post your non working formula

 

[RagDyer]

Countif() addresses one contiguous range at a time, so you would need to add
them together to obtain your results.
For example, if the data to be checked (counted) was in B1,
And you have 3 non-contiguous ranges, try:

=COUNTIF(D1214,B1)+COUNTIF(G14:G16,B1)+COUNTIF(I16:I18,B1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I was trying to use COUNTIF to see if a value in a cell was also in a range
of cells, but the range is non-contiguous. For good reason, the range is
sorted in a certain way. The commas between the contiguous ranges seemed
the throw the formula off. Enclosing the range in paranthesis didn't help,
neither did naming the range and using the name. There is little
documentation about this in Help. Any Ideas?

 

[Bradley Dawson]

OK, thanks. I was trying to avoid something like
"=OR($B$27 = D3, $B$27 = D5, $B$27 = D6, $B$27 = D10, $B$27 = D13, $B$27 =
D14, $B$27 = D20, $B$27 = D21)"
only much longer.
Other than getting a few contiguous ranges in, it doesn't look like the
formula would be any easier to understand or much shorter. I found another
work around, but thanks again.

 

[Peo Sjoblom]

If the value in B27 is numeric you can use

=SUMPRODUCT(--(LARGE(MyName,ROW(INDIRECT("1:"&COUNT(MyName))))=B27))

(courtesy of David Hager)

where MyName would be the non-contiguous ranges under one name (select the
cells with mouse holding down control, then do insert>name>define and type a
name)