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
Here is your answer:
http://www.mrexcel.com/tip031.shtml
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
