MrExcel Publishing
Your One Stop for Excel Tips & Solutions

multiple conditions and COUNTIF

Posted by Lu Wood on January 15, 2002 12:19 PM

I was SO glad to find this forum.
I have a spreadsheet for a college project that needs to give
the number of instances cells in one column are greater than one amount but less than another.

For example my formula was: +COUNTIF(A1:A12,">5000,<10000")I also need to compute >10000 but <15000,
and so on up until 50000.

The COUNTIF function is only computing the one variable
(<) and doesn't even factor in the rest. COUNTIF seemed appropriate but doesn't appear to handle more than one criteria.
I can't locate another function Please advise...
I have 8 pages to compute formulas on and then need to build charts. I've only got a week and this has me stuck!!
Pardon the panic.

Posted by Aladin Akyurek on January 15, 2002 1:52 PM

Lu --

In E1 enter: 5000
In F1 enter: 10000
In E2 enter: 10000
In F2 enter: 15000

In G1 enter: =COUNTIF($A$1:$A$12,">="&E1)-COUNTIF($A$1:$A$12,">"&E2)

This will count numbers in $A$1:$A$12 which are GREATER THAN OR EQUAL to E1 and LESS THAN E2.

Copy down this formula for other criteria in E and F.



Posted by Lu Wood on January 23, 2002 2:16 PM

Worked great! Thanks Aladin