# count the occurence within a range of numbers within a colum

L

#### Legacy 57334

##### Guest
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### MorganO

##### Active Member
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

L

#### Legacy 57334

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

thanks!

Replies
1
Views
73
Replies
11
Views
318
Replies
3
Views
430
Replies
7
Views
226
Replies
6
Views
261

1,191,170
Messages
5,985,066
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back