Could anyone help me with a formula in Google Sheets that calculates the amount of rows that have both: 1) a specific value and 2) an empty cell?
To specify: Cells C2:I101 contain game results of a boardgame competition. There are both 4 player and 3 player tables, so in the case of a 3 player game, the cells in column I and J are empty. All players have their own unique player ID, which is found in column A. I need to know how many times a player took part in a 3 player game. These results should appear in column L.
See my spreadsheet below. Thank you in advance!
https://docs.google.com/spreadsheets/d/1kg-RZC3NpG-bd4Ckik7_I_1iVwiyKxnfevKKnyLmdXg/edit?usp=sharing
I tried several formula:
=COUNTIF(AND(C$2:J$168=A3,I$2:I$168=""),C$2:J$168)
returns '1' for each row.
=SUMPRODUCT((C2:I168=A2)*(COUNTBLANK(C2:I168)>0))
returns #N/A.
=COUNTIFS(C2:J168,A2,I2:I168,I2:I168>1)
returns #VALUE.
=COUNTIFS(C2:J6,A2,I2:I6,">1")
returns #VALUE.
EDIT:
I managed to solve the issue myself. I rearranged the columns, then created a helper column that counted the amount of cells in each row with values (=COUNT(D2:G2). Three values indicates a 3-player game, four values a 4-player game. Then I used the following formula to count all rows with both a specific Player ID and an empty cell:
=SUMPRODUCT((D$2:G$168=A2)*(H$2:H$168=3))