Thanks:  0
Likes:  0

# Thread: syntax : multiple ranges in countif

1. 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

2. 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"

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.

appears to compute the desired count.

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

3. amazed ?

edit --> arrrgh, yup

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

4. fantastic

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 ]

5. > using your example and extrapolating

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

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

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

thanks

## User Tag List

#### Posting Permissions

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