| Team | Oppo | C | D | E | F |
|---|---|---|---|---|---|
| ATL | BOS | BOS | BOS, NYK | ||
| ATL | NYK | NYK | |||
| ATL | SAS | ||||
| ATL | MIL | ||||
| ATL | NYK | ||||
| ATL | LAL | ||||
| ATL | LAC | ||||
| CHI | BOS | ||||
| CHI | LAL |
We have a table of this format in google sheets, and we need to put an equation in cell
F1 that is the count of rows (using columns Team, Oppo, i.e. A and B) that are the count of rows where Team == "ATL" and Oppo in D. For the latter condition, we can likewise use the equation Oppo in E1 where E1 is a string concatenation of column D.
=countifs("sheet!A:A", "ATL", "sheet!B:B", __heres the part needed__), using two ranges and two criteria, however I am not sure how / if it’s possible to do the second criteria for the value in column D, in cell E1 criteria. Is this possible in Google Sheets?
The output should be 3 as there are three rows (1, 2, 5) where both conditions are met.
>Solution :
Here’s one way to do that:
=SUMPRODUCT(COUNTIFS(A2:A,"ATL",B2:B,TOROW(D2:D,1)))
We are essentially counting each condition individually
F1 is the count of A2:A="ATL" and B2:B="BOS"
G1 is the count of A2:A="ATL" and B2:B="NYK"
And then we are summing them. In this context SUMPRODUCT() is just a shorter way of writing SUM(ARRAYFORMULA())