Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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% ? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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% ? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Counting Numbers in an Interval | Excel Worksheet Functions | |||
Find Median of Positive numbers only in Range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |