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

 

[averageexceluser01]

There may be a quicker way to do this with countif, but the sumproduct function works great.

Use this:

Band A =SUMPRODUCT(--($E$1:$E$125>=1),--($E$1:$E$125<=5))
Band B =SUMPRODUCT(--($E$1:$E$125>=6),--($E$1:$E$125<=20))
Band C =SUMPRODUCT(--($E$1:$E$125>=21),--($E$1:$E$125<=29))
Band D =SUMPRODUCT(--($E$1:$E$125>=30),--($E$1:$E$125<=125))

 

[averageexceluser01]

That is if $E1:E125 contains all of your data. Otherwise replace this with the entire range.