Counting if numbers fall within a specified range

G

Guest

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
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

COUNTIF Cells in Range? 4
counting cells that fall within a certain date range 9
countif - sumif 1
Counting text which meets a criteria 2
Need to count a set of numbers in a range 4
Count IF multiple criteria 7
Count cells within a date range 7
Summing values within a range 7

Top