# COUNTIF with multiple criteria

This is a discussion on COUNTIF with multiple criteria within the Excel Questions forums, part of the Question Forums category; How can one use COUNTIF with a combination of two or more criteria (using OR, AND etc)? For example: =COUNTIF(A1:A100 ...

1. ## COUNTIF with multiple criteria

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.

2. ## Re: COUNTIF with multiple criteria

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!)

3. ## Re: COUNTIF with multiple criteria

very common question on the board - use sumproduct:

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

hth
kevin

4. ## Re: COUNTIF with multiple criteria

Of course your way is probably better!

5. ## Re: COUNTIF with multiple criteria

... or even

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

6. ## Re: COUNTIF with multiple criteria [Thanks!]

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

-Best Regards,
S.T.

7. ## Re: COUNTIF with multiple criteria

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

8. ## Re: COUNTIF with multiple criteria

Originally Posted by kskinne
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))}

9. ## Re: COUNTIF with multiple criteria

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

#### Posting Permissions

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