MrExcel Publishing
Your One Stop for Excel Tips & Solutions

counting numbers within a range


Posted by Justin on August 20, 2001 2:11 PM

I am trying to figure out how to count (not sum) numbers that are within a certain range - countif only lets you give a simple critera - that doesn't work.

example

data

5
8
12
30

output: Num Cells between 1-10 = 2
Num Cells between 10-20= 1
Num Cells between 20-30 =1

help!


Posted by Aladin Akyurek on August 20, 2001 2:30 PM

Justin,

=SUMPRODUCT(($A$2:$A$100>=B2)*($A$2:$A$100<=C2))

where A2:A100 houses your numeric data, B2 a lower limit, and C2 an upper limit. Note that I used an inclusive between, otherwise adjust.

Aladin

=======