count cells that fall within a range of numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello - I would like to have a function that looks at a range of cells, and tells me how many of the cells have values that fall within a range of numbers. I tried the COUNTIF function, but I don't know how to put a range of numbers in the criteria.

example: range of cells contain the values: 2, 18, 21, 43

I want a calculation that returns # of cells that have a value that falls between 14 and 30 (answer would be 2 in this example).

I'm using excel 2000.

Thanks, Robin
 
Robin,

If you want the range 14 <= X <= 30 use:
=COUNTIF(A1:A100,">=14") - COUNTIF(A1:A100,">30")
OR
=SUMPRODUCT((A1:A100>=14)*(A1:A100<=30))
includes 14 and 30

for 14 < X < 30 use:
=COUNTIF(A1:A100,">14") - COUNTIF(A1:A100,">=30")
OR
=SUMPRODUCT((A1:A100>14)*(A1:A100<30))
excludes 14 and 30

Dan E


Robin Wright said:
Hello - I would like to have a function that looks at a range of cells, and tells me how many of the cells have values that fall
Click to expand...
within a range of numbers. I tried the COUNTIF function, but I don't know how to put a range of numbers in the criteria.
 
Robin Wright said:
Hello - I would like to have a function that looks at a range of cells,
Click to expand...
and tells me how many of the cells have values that fall within a range of
numbers. I tried the COUNTIF function, but I don't know how to put a range
of numbers in the criteria.
example: range of cells contain the values: 2, 18, 21, 43

I want a calculation that returns # of cells that have a value that falls
Click to expand...
between 14 and 30 (answer would be 2 in this example).
I'm using excel 2000.

Thanks, Robin
Click to expand...

For numbers in cells A1:A4 you could use
=SUMPRODUCT((A1:A4>14)*(A1:A4<30))
or
=SUMPRODUCT((A1:A4>=14)*(A1:A4<=30))
depending on whether you consider 14 and 30 to be inside or outside the
range.
 
Back
Top