Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 23rd 12, 02:50 PM
Banned
 
First recorded activity by ExcelBanter: May 2012
Posts: 1
Default Newbie here - hope you can help

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

  #2   Report Post  
Old May 23rd 12, 06:32 PM
Senior Member
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 663
Default

Quote:
Originally Posted by woodylake View Post
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.
Attached Files
File Type: zip AgeCount.zip (4.7 KB, 44 views)
  #3   Report Post  
Old May 23rd 12, 11:01 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 829
Default Newbie here - hope you can help

"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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Real Newbie newbie question Dave New Users to Excel 0 January 10th 07 08:55 PM
newbie.... need some code... I HOPE! beechum1 Excel Programming 8 January 17th 06 08:47 PM
Hope Someone Can Help... Tom Ogilvy Excel Programming 0 October 4th 05 12:54 PM
Last Hope -HELP!! Dick Kusleika[_3_] Excel Programming 3 July 1st 04 06:33 PM
Last Hope -HELP!! Bob Phillips[_6_] Excel Programming 0 June 29th 04 09:08 PM


All times are GMT +1. The time now is 11:50 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017