Excel: Count cells with "Apple" only in rows that matches the name

0
votes

I'm trying to create a spreadsheet that counts the number of a particular occurrence on a row only when it matches the name.

       Col A       Col B    Col C    Col D
Row 1  **Jane**    Apple    Orange   Apple
Row 2  **Bob**     Orange   Apple    Orange
Row 3  **John**    Apple    Apple    Apple
Row 4  **Joe**     Orange   Apple    Apple

I was thinking =IF(A:A=A2, COUNTIF($B$2:$D$4,"Apple"), "NULL") will work but it's not giving the expected result.

The expected result in the above formula is 1

1
excelexcel-formula
Why is the answer 1? – SJR
the name 'Jane' is in there only once, anyway, so a lot of formulas will deliver that result. Can you post a more representative data sample? '1' would also be the result for the other rows, right? Why 1? There are two apples on that row. – teylyn
The answer should be '1' cos cell reference A2 is Bob and the word 'Apple appears just once in its row. – Kev
The idea of the formula is 'if A2 matches any of the names in Column A [if Bob matches any name in list {Jane, Bob, John, Joe}], count how many times Bob got "Apple"(Count the occurrences of Apple on Row 2). – Kev

1 Answers

0
votes

One way is using SUMPRODUCT, viz:

=SUMPRODUCT(($A$2:$A$5="Bob")*($B$2:$D$5="Apple"))

To make it a bit more flexible, could do something like this:

=SUMPRODUCT(($A$2:$A$5=F3)*($B$2:$D$5=G2))

enter image description here