MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dual criteria for COUNTIF


Posted by jim pruszka on March 16, 2001 7:46 AM

I am wondering how I can use the function COUNTIF to
count the numbers in a list that are "greater than 5
but less than 10", for example. It's a dual criteria
situation that I can't for the life of me figure out.
I've tried colons and semi-colons, commas and the word
"AND" between the two criteria but nothing works.
If there is a better function for doing this I would
be open to that also. Thanks.


Posted by Big Bob on March 16, 2001 8:09 AM

What about using countif more than once
total-countif("<10")-countif(">5")
Sorry about the syntax not being right but you should get the idea
HTH Big Bob

Posted by Loren on March 16, 2001 8:14 AM

Posted by Michelle on March 16, 2001 8:14 AM

Syntax should be:
=COUNTIF(range,"<10")-COUNTIF(range,">5")

Posted by Mark W. on March 16, 2001 8:31 AM

Bob and Michelle...

My reading of Bob's suggestion was:

=COUNT(range)-COUNTIF(range,"<10")-COUNTIF(range,">5")

...rather than...

=COUNTIF(range,"<10")-COUNTIF(range,">5")

...but neither of these calculate the needed
intersection.

If you apply these formulas to a range containing
{1;2;3;4;5;6} the former will produce -1 and
the latter will produce 5. The intersection
should be 1.

Posted by Big Bob on March 16, 2001 8:46 AM

Re: Bob and Michelle...

O.K. How does =COUNT(range)-COUNTIF(range,"<=5")
-COUNTIF(range,"=>10") Notice the change in the
arguements

Posted by Aladin Akyurek on March 16, 2001 8:48 AM

You can use the following array-formula that you must enter by hitting CONTROL+SHIFT+ENTER at the same time.

=SUM((A1:A8>5)*(A1:A8 LessThan 10))

Note. Replace LessThan by the appropriate Excel symbol.

Aladin

Posted by Mark W. on March 16, 2001 8:56 AM

Bob...

=COUNT(range)-COUNTIF(range,"=>5")-COUNTIF(range,"<=10")
gives you 0. Intersections are tricky...and more
easily accomplished using an array formula as
suggested by others making earlier follow-up
postings. The only reason I commented was to get
you thinking about test cases.

Posted by Michelle on March 16, 2001 9:07 AM

Re: Bob...

I was just trying to put the right syntax on Big Bob's suggestion.

Posted by big bob on March 16, 2001 9:32 AM

Re: Bob...


Sorry its been a long day.In England now its 5:30 p.m. Hopefully the right way is
=count(range)-countif(range,"<=5")-countif(range,"=>10")


Posted by jim pruszka on March 17, 2001 6:18 AM

Thanks, Aladin, your suggestion works perfectly.
Jim