Search code examples
google-sheets

In Google Sheets, for =countifs condition, countif column in another column

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)))

    enter image description here

    We are essentially counting each condition individually

    enter image description here

    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())