# [b]Help[/b]

#### petenorriss

##### New Member
Hi all

Im trying to do a report and I am really strugelling. Column A has a load of ages of staff. I am trying to do formula that will auto count these ages for me. I want them grouped so I know who is under 30, between 30 and 40, 41 and 50 etc.

I have used the countif function for other things but cant make it work for this. Is there any formula I can use. Even if its only for one group, it would be useful as I could adapt it for other groups.

Any help is appreciated.

Cheers

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Is this what you are looking for?
Book1
ABCDE
126MinMaxHowmanyinGroup
25420302
332SUMPRODUCT(--(A1:A9>=C2),--(A1:A9<=D2))
462
532
633
731
845
925
Sheet1

Ken your a star.

I have one other querie, perhaps a bit more complicated. I have another column that gives the date staff members joined a grade, ie 01/01/2000

Is it possible to do a formula to group them by length of service in grade. IE - 5-10 years, 11-15 years etc?

petenorriss said:
...
Im trying to do a report and I am really strugelling. Column A has a load of ages of staff. I am trying to do formula that will auto count these ages for me. I want them grouped so I know who is under 30, between 30 and 40, 41 and 50 etc.

...
Book1
ABCDEF
1AgeFREQUENCYCOUNTIFSUMPRODUCT
2410555
32930222
45740555
52350222
6401.00E+308
745
847
924
1054
1148
1224
1338
1442
1520
Sheet1

D2, using FREQUENCY()...

=INDEX(FREQUENCY(\$A\$2:\$A\$15,\$C\$2:\$C\$5),ROW()-ROW(D\$2)+2)

E2, using COUNTIF()...

=COUNTIF(\$A\$2:\$A\$15,">"&C2)-COUNTIF(\$A\$2:\$A\$15,">"&C3)

F2, using SUMPRODUCT()...

=SUMPRODUCT(--(\$A\$2:\$A\$15>C2),--(\$A\$2:\$A\$15<=C3))

Replies
4
Views
116
Replies
2
Views
218
Replies
6
Views
192
Replies
4
Views
140
Replies
9
Views
327

Threads
1,203,052
Messages
6,053,234
Members
444,648
Latest member
sinkuan85

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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