syntax : multiple ranges in countif

[Chris Davison]

Hi all,

I should know this but just can't get it

I'm trying to use countif on 24 seperate ranges (A1:A7 and A11:A17 and A21:A27 and A31:A37 etc etc etc)

do I have to use 24 seperate countif statements and add them or can I simplify it ?

=COUNTIF(A1:A7,5)+COUNTIF(A11:A17,5)+COUNTIF(A21:A27,5)+COUNTIF(A31:A37,5) etc etc etc

many thanks
Chris

[Aladin Akyurek]

Chris,

> I'm trying to use countif on 24 seperate ranges (A1:A7 and A11:A17 and A21:A27 and A31:A37 etc etc etc)

>do I have to use 24 seperate countif statements and add them or can I simplify it ?

=COUNTIF(A1:A7,5)+COUNTIF(A11:A17,5)+COUNTIF(A21:A27,5)+COUNTIF(A31:A37,5) etc etc etc

I was going to say yes... But I couldn't resist trying out an idea stirred up by an another recent question at this board & being amazed by your

"I should know this but just can't get it"

that is heading your question:

Consider the following sample:

{5;2;5;"";5;8;5.00;"zxa";"";"";5;5;7}

in A1:A13, where "" stands for an empty cell.

I'm interested in counting 5's in A1:A3 and A11:A13.

=SUMPRODUCT((COUNTIF(INDIRECT(ADDRESS({1;11},1)&":"&ADDRESS({3;13},1)),5)))

appears to compute the desired count.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-03 11:18 ]

[Chris Davison]

amazed ?

edit --> arrrgh, yup

[ This Message was edited by: Chris Davison on 2002-03-03 11:59 ]

[Chris Davison]

fantastic

using your example and extrapolating

=SUMPRODUCT((COUNTIF(INDIRECT(ADDRESS({1;11;21;31;41;51},1)&":"&ADDRESS({3;13;23;33;43;54},1)),5)))

etc etc for the 24 ranges is much much shorter



Ohhh - I just changed the last 54 to 59 and populated a few more "5"s and it still works - had you anticipated it working without equal-sized ranges ? ie cos it's not an array formula ?

wow

[ This Message was edited by: Chris Davison on 2002-03-03 11:52 ]

[Aladin Akyurek]

> using your example and extrapolating

=SUMPRODUCT((COUNTIF(INDIRECT(ADDRESS({1;11;21;31;41;51},1)&":"&ADDRESS({3;13;23;33;43;54},1)),5)))

etc etc for the 24 ranges is much much shorter

Yes, that was exactly my intent.

> Ohhh - I just changed the last 54 to 59 and populated a few more "5"s and it still works -

Yes, it will.

> does this mean it still works without equal-sized ranges ?

The issue of "unequal-sized ranges" does not arise in this formula. What happens is that COUNTIF gets fed with different ranges at a time, each time with the same condition (looking for 5). The result is a constant array consisting of individual counts produced by COUNTIF, which SUMPRODUCT then sums in its turn.

> ie cos it's not an array formula ?

It's an inherently array formula (which does not need entering with the control+shif+enter combination though). If you'd like to know more about SUMPRODUCT, you might want to have a look at

hhtp://www.mrexcel.com/wwwboard/messages/8961.html

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-03 12:08 ]

[Chris Davison]

bookmarked..... I'd actually been searching for that entry for a while

thanks