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 have to use please

Hi,

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

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

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

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

S.

"woodylake" wrote:
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?

Hello!

Using the COUNTIF and COUNTIFS formula will allow you to count how many a

1) under30
2)30 to 40
3)40 to 50
4)50 to 60
5)older

So for under 30 and above 60, it's just a COUNTIF formula:
=Countif(Range, criteria)
Range is just column where the age is, then criteria is "<30", and "60"

For the middle ages, just do the COUNTIFS Formula:
=countifs(creteria_range1,Criteria1,Criteria_range 2,Criteria2...)
IN this case, Range is the same for all, but the criteria 1 is "30", then criteria 2 is "<40", so on and so forth.

Hope this helps!