Count occurences in columns, with criteria?


Posted by Al on December 07, 2000 9:22 AM

Hi all,
This is probably a very simple one for you, certainly compared to others on the Board.

I have two columns, Gender and Age. The Gender column contains either the word "male" or "female". The Age Group Column has either a "1", "2" or "3". I need a formula to count say, how many males are in age group 3. Sounds simple but I'm unfamilar with Excel.

I'd appreciate any help you can give.

Regards

Al

Posted by Loren on December 07, 2000 9:54 AM


check this out....SumIf example
http://www.mrexcel.com/tip031.shtml

Posted by Ivan Moala on December 07, 2000 9:58 AM

Assuming gender in ColA and Age in ColB and
rnage 2-15 then
The array formula =SUM((A2:A15="male")*(B2:B15=1))
should help you.

Enter via Ctrl+Sht+Enter


Ivan



Posted by Aladin Akyurek on December 07, 2000 10:06 AM

Another Solution:

Assuming that you have the label Gender in A1 and the label Age in B.

I would name D1 Gcrit and E1 Acrit. Enter F in D1 and 3 in E1, for example.
Put the following in C2

=(A2=Gcrit)*(B2=Acrit)

and copy down as far as needed.

Then put in D2:

=sum(C:C)

Aladin