Counting if numbers fall within a specified range
 

For a range of cells containing percentages, how can I use COUNTIF to count
those cells whose values fall between 90% and 110% ?

You could count the number = .9 and subtract the ones 1.10
=countif(a1:a10,"=.9") - countif(a1:a10,"1.1")

Or you could count the ones that are trapped between those numbers:
=SUMPRODUCT(--(A1:A10=0.9),--(A1:A10<1.1))

=sumproduct() likes to work with numbers. The -- converts trues and falses to
1's and 0's.





KG wrote:

For a range of cells containing percentages, how can I use COUNTIF to count
those cells whose values fall between 90% and 110% ?

--

Dave Peterson

One way,
=SUMPRODUCT((A1:A100=0.9)*(A1:A100<=1.1))
Regards,
Alan.
"KG" wrote in message
...
For a range of cells containing percentages, how can I use COUNTIF to
count
those cells whose values fall between 90% and 110% ?

Assuming you want to exclude values that equal 90 and 110, try this:

=COUNTIF(A1:A100,"90)-COUNTIF(A1:A100,"109")

Change the range A1:A100 to suit your needs


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=466499

Thank you. I'll try both methods

"Dave Peterson" wrote:

You could count the number = .9 and subtract the ones 1.10
=countif(a1:a10,"=.9") - countif(a1:a10,"1.1")

Or you could count the ones that are trapped between those numbers:
=SUMPRODUCT(--(A1:A10=0.9),--(A1:A10<1.1))

=sumproduct() likes to work with numbers. The -- converts trues and falses to
1's and 0's.





KG wrote:

For a range of cells containing percentages, how can I use COUNTIF to count
those cells whose values fall between 90% and 110% ?

--

Dave Peterson