[b]Help[/b]

[petenorriss]

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

 

[ken2step]

Is this what you are looking for?

Book1
	A	B	C	D	E
1	26		Min	Max	HowmanyinGroup
2	54		20	30	2
3	32				SUMPRODUCT(--(A1:A9>=C2),--(A1:A9<=D2))
4	62
5	32
6	33
7	31
8	45
9	25
Sheet1

 

[petenorriss]

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?

 

[Aladin Akyurek]

...
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
	A	B	C	D	E	F
1	Age			FREQUENCY	COUNTIF	SUMPRODUCT
2	41		0	5	5	5
3	29		30	2	2	2
4	57		40	5	5	5
5	23		50	2	2	2
6	40		1.00E+308
7	45
8	47
9	24
10	54
11	48
12	24
13	38
14	42
15	20
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))