Count if for two cells

[Statz]

I need a formula that will count how many cells that have "x" showing up only if another cell in the same row is "y".


For example,

I need to count how many times "sports" shows up in column B ONLY if column D has the name David in the same row.


Below is a thread I made recently that is similar to the question I have now however the link below shows how to sum. I just need to count the cells, not sum.


http://www.excelforum.com/excel-gene...-criteria.html

[alansidman]

Assuming the values Sports would be in Column A and David in Column B. Data is in rows 1-10

=sumproduct((A1:A10="Sports")*(B1:B10="David"))

[Johnny_Drama]

Not sure how large your excel file is, but how about a column that indicates whether there is Y, whether there is X and whether there is both X and Y? Then sum up that column...

In the below code, substitute "y" for "david", exchange b1 for the first cell of column Y, substitute "x" for "sports", exchange a1 for the first cell of column X.

Then exchange "can't find X" with 0 and "cant find Y" with 0, and "Has X and Y" with 1.

Auto format from the top to the bottom of your data and sum up that column.

Code to understand what I'm doing:

Please Login or Register  to view this content.

Code Formatted for you:

Please Login or Register  to view this content.

[Johnny_Drama]

Wait a minute,

I just realized how easy this is to do. Just use a simple countifs

a1:a10 is the range for sports and b1:b10 is the range for david

Please Login or Register  to view this content.

[alansidman]

Countifs is a 2007 function. OP is using 2003.

Alan

[Statz]

Assuming the values Sports would be in Column A and David in Column B. Data is in rows 1-10

=sumproduct((A1:A10="Sports")*(B1:B10="David"))

thank you this worked!!