CODEWITHSUNDEEP
excel
otterdog2000
otterdog2000

Reputation: 363

Countif in a row with row criteria

In Excel, I have a list of golfers and their scores. I want to find a way to count how many times they scored a 4 when playing.

So in column B, I have a list of golfers. Then in columns C through T I have their scores. The golfers names are listed more than once. So I want a way to match their name in the list then count how many times they scored a 4 for that day.

I tried INDEX-MATCH and SUMPRODUCT, but couldn't get a proper result. Sample data:

B     C D E F G H
-----------------
Brian 2 3 4 4 2 3
Greg  3 4 3 3 4 5
Eric  4 3 3 4 5 2
Greg  4 2 2 3 4 5
Eric  4 4 3 3 2 2

My desired solution: With a list of players on another sheet, I want a formula that will count the total instances of the number 4 for each player.

Upvotes: 0

Views: 90

Answers (1)

teylyn
teylyn

Reputation: 35900

Create a list of unique names, for example in column J, then you can use a Sumproduct like this:

=SUMPRODUCT(($C$2:$H$6=4)*($B$2:$B$6=J2))

enter image description here

The data can be on a different sheet, of course.

Upvotes: 2

Related Questions