MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.



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

check this out....SumIf example

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


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


and copy down as far as needed.

Then put in D2: