Excel – how do you count using if and sumproduct

countexcelformulaif statement

I have a database of people who may or may not have multiple entries and I'd like to know how to count the total number of people who are male who meet another category using a formula. I current use the 

=SUMPRODUCT((MelanomaEth="U")/COUNTIF(MelMRN,MelMRN&"")))

formula to count the number of unique entries with a "U" in the MelanomaEth column. However, I'd like to go further and determine how many of these U's are males and females.

I tried to use:

=IF(MelSex="M",SUMPRODUCT((MelanomaEth="U")/COUNTIF(MelMRN,MelMRN&"")))

but it gives me the incorrect number.

Here is an "dummy" sheet:

MRN  Date   Sex  Ethnicity
A   8/1/2013    M   U
B   8/2/2013    F   N
C   8/2/2013    F   N
A   9/2/2013    M   U
A   9/3/2013    M   U
C   8/31/2013   F   N
B   8/15/2013   F   N
D   10/5/2013   M   U

If I wanted to know the number of unique names who are M and U, I should get 2. The number of names who are F and U should be 0, FN should be 2, and 0 MN.

Any suggestions would be appreciated.

Thanks!

Best Answer

Try this:

=SUMPRODUCT(((MelanomaEth="U")/COUNTIF(MelMRN,MelMRN&""))*((MelSex="M")/COUNTIF(MelMRN,MelMRN&""))

What your looking for is a sumproduct with multiple criteria. Usually the format is something like this:

= SUMPRODUCT((RANGE CONDITION)*(RANGE2 CONDITION2))

= SUMPRODUCT(( D1:E5 > 1 )*( D1:E5 < 10 ))

If a condition is false, then the whole statement is false and wont be counted.

Since I'm not sure what your names represent I can't be sure the code above will work for you.

Related Topic