count the occurence within a range of numbers within a colum

[Legacy 57334]

Hi,

Column E has numbers bettween 1 - 125, in no particular order.

I want to count the number of times the data in this column fall with bands which I set. In this case:
Band A = 1-5
Band B = 6-20
Band C = 21-29
Band D = 30-125

As an example if the data in column E was:
20
3
49
98
4
3
22
The count for band A would be 3.
The count for band B would be 1
The count for band C would be 1
The count for band D would be 2

Quick and easy to do in your head when there in only a little data, but when there is lots....

Thanks
Joel

 

[MorganO]

You can use SUMPRODUCT to do what you are looking for.

For Band 1 (1-5):
=SUMPRODUCT((E1:E125>=1)*(E1:E125<=5))

For Band 2 (6-20)
=SUMPRODUCT((E1:E125>=6)*(E1:E125<=20))

For Band 3 (21-29)
=SUMPRODUCT((E1:E125>=21)*(E1:E125<=29))

For Band 4 (30-125)
=SUMPRODUCT((E1:E125>=30)*(E1:E125<=125))

Take care.

Owen

 

[Legacy 57334]

Owen, I only just realised i didnt thank you for this, it works great, and its simple enought that i understand it.

thanks!