Counting data sets based on multiple criteria

**hjb0802** · 04-29-2010, 10:10 AM

Hi,
I have been trying to figure out a formula that will allow me to count people in my data set by age, gender, and ethnicity. does anyone know what kind of formula i use and how to go about writing it? For instance, I want to know how many people (out of 679) are 15y/o, Female, and Hispanic. Thank you!

**NBVC** · 04-29-2010, 10:13 AM

If you are looking for specific information, then use Sumproduct

e.g. =Sumproduct(--(AgeRange=Age),--(GenderRange=Gender),--(EthnicityRange=Ethnicity))

replace ranges and references with actual cell ranges and cell references...

If you want to create a summary table, have a look at Pivot Tables.

**hjb0802** · 04-29-2010, 10:19 AM

Thank you. I tried that formula, and it works, but I get an incorrect count. When the output should have been 2, I got 0....any idea why? This has been my main problem all along...

**NBVC** · 04-29-2010, 10:21 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**hjb0802** · 04-29-2010, 10:33 AM

Attached is an example of my spreadsheet. I want to count
# of 12y/o white females
# of 12 y/o hispanic females
# of 13 y/o white males
# of 13 y/o hispanic males,
etc.
In other words, I want a formula that counts a particular set of data only if it contains all three of the criteria--age, gender, ethnicity-- that I input.