COUNTIF with multiple criteria

Swamp Thing

Active Member
Joined
Aug 16, 2002
Messages
313
How can one use COUNTIF with a combination of two or more criteria (using OR, AND etc)? For example:

=COUNTIF(A1:A100 , "AND( >10 , <20 ) " )

does not work, but there must be a way to count the values that lie between 10 and 20.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Think you need to use a CSE/Array formula. ie
=if(A1:A100>10,if(A1:A100<20,count(A1:A100)))

And then hit Control + Shift and Enter!! (this bit is the array formula bit!)
 
Upvote 0
very common question on the board - use sumproduct:

=SUMPRODUCT((A1:A100>10)*(A1:A100<20))


hth
kevin
 
Upvote 0
Re: COUNTIF with multiple criteria [Thanks!]

Thanks for all your inputs. I tried the SUMPRODUCT and it worked.

-Best Regards,
S.T.
 
Upvote 0
Good - just so you know, the * operator is used to include any value that meets the first criteria AND the second criteria. if you want to include values that meet EITHER of the criteria in your formula, use the + operator


kevin
 
Upvote 0
kskinne said:
Good - just so you know, the * operator is used to include any value that meets the first criteria AND the second criteria. if you want to include values that meet EITHER of the criteria in your formula, use the + operator
kevin

Careful, you don't want to use + with this SUMPRODUCT function 'cause it'll provide an incorrect count when both condtions are TRUE (e.g., TRUE+TRUE = 2). In that case it's best to use IF in an array formula...

{=COUNT(IF((range1=value1)+(range2=value2),1))}
 
Upvote 0
Yes it will, my apologies if I was unclear in my post - I was only including it as an additional piece of info for future reference, in case he needed to use sumproduct in a different problem with different circumstances.

Regards
kevin
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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