Newbie here - hope you can help

[woodylake]

I have a column of ages in my club on a spraedsheet like
AGE
21
32
45
55
50
45
19
32
etc
About 150 members, I want to find out how many are
1) under30
2)30 to 40
3)40 to 50
4)50 to 60
5)older

for the answers to come out in anither column, whats the formulae i hav
to use pleas

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

I have a column of ages in my club on a spraedsheet like
AGE
21
32
45
55
50
45
19
32
etc
About 150 members, I want to find out how many are
1) under30
2)30 to 40
3)40 to 50
4)50 to 60
5)older

for the answers to come out in anither column, whats the formulae i hav
to use please

Hi,

See the attached. The formulas are in the yellow cells and the cells i
covers are coloured green.

If you need more than 150 members then you'll have to change the rang
lengths in the formulas to accommodate this.

I'm used SUMPRODUCT rather than SUMIFS as you didn't stipulate whic
version of Excel you're using and SUMIFS will not work in anything prio
to Excel 2007.

Hope that helps.
Feel free to ask if you need a full explanation of how the two formula
I've used work.

S

+-------------------------------------------------------------------
|Filename: AgeCount.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=392
+-------------------------------------------------------------------

 

[joeu2004]

I have a column of ages in my club [....]
About 150 members, I want to find out how many are
1) under30
2)30 to 40
3)40 to 50
4)50 to 60
5)older

Suppose your data are in A2:A151, and your labels above are in B1:B5 [1].

Then select C1:C5 and array-enter the following formula (press
ctrl+shift+Enter instead of just Enter):

=FREQUENCY(A2:A151,{29,39,49,60})


-----
[1] I think the labels should be
under 30
30 to 39
40 to 49
50 to 60
over 60
The point is: having both 30 to 40 and 40 to 50, for example, is ambiguous.
Where does 40 really fall?