Counting if numbers fall within a specified range

K

KG

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

Dave Peterson

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.
 
C

Cutter

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
 
K

KG

Thank you. I'll try both methods

Dave Peterson said:
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.
Click to expand...
 
Top