syntax : multiple ranges in countif

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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
:)
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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
 
Upvote 0
> 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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top