Search code examples
google-sheets

Google Sheets: OR operators within the multiple criteria of COUNTIFS?

I'm doing an analysis of a large Google Sheets spreadsheet where I need to tally up all instances where column B has values (a, b OR c) AND column C has values (d, e OR f).

Both columns have a wider range of values, I just need all instances where there'd be a AND d, a AND e, a AND f, b AND d, b AND e, etc.

My go to would be to use COUNTIFS, but I'm not sure how the OR operator would work in the syntax.

Solution

  • Here you have another option with SUMPRODUCT and matching the values with the range with the chosen values:

    =SUMPRODUCT(IFNA(XMATCH(B:B,E:E)>0),IFNA(XMATCH(C:C,F:F)>0))

    You could also put the values in the formula itself:

    =SUMPRODUCT(IFNA(XMATCH(B:B,{"a","b","c"})>0),IFNA(XMATCH(C:C,{"d","e","f"})>0))

    enter image description here

    Explanation:

    XMATCH returns the position of an element in a range or group of elements. For example if it looks for "B" in "A","B","C" it will return 2. If it's not found it returns an error (that's handled with IFNA).

    Then, the result is checked to see if it's bigger than 0 (meaning it's found). This returns TRUE or FALSE. Or, in this case, this equals to 1s and 0s.

    SUMPRODUCT multiplies the two columns row by row. Only returning 1 when it's 1 in both columns (meaning that the condition is met in both). And then sums all these final products of 1s and 0s