Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: Count occurences in columns, with criteria?

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

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

Re: Count occurences in columns, with criteria?

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


Re: Count occurences in columns, with criteria?

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.