Search code examples
google-sheetsformula

Issue with CountIFS trying to use a range as criteria

I have a dataset with two columns, with each entry listing both a caseworker and a company. So far, I have set things up so that I can see a count of how many times each caseworker works on each company. I've done this by using a =COUNTIF function, as shown in the screenshot below.

A screenshot of a google sheet.

My question is this: how do I count the number of times a given caseworker has worked with a given group of companies? I tried to do it with the following formula, but it simply returns 0 when I reference the grouped range in J2:J4:

=CountIFS('All Report'!$B$1:$B, $A2, 'All Report'!$A$1:$A, $J$2:$J$4)

Here's a link to a copy of my Google Sheet: Link

Solution

  • Answer

    One immediate issue is that the company groups listed on your Main sheet are comprised of TestGroupA through TestGroupF, whereas the companies in your All Report sheet are GroupTestA through GroupTestF (the words are reversed).

    Once that is corrected, the following formula should produce the desired behaviour when placed into cell G2 and autofilled into the cells below it.

    =SUM(COUNTIFS('All Report'!$B$1:$B, $A2,'All Report'!$A$1:$A,$J$2),COUNTIFS('All Report'!$B$1:$B, $A2,'All Report'!$A$1:$A,$J$3),COUNTIFS('All Report'!$B$1:$B, $A2,'All Report'!$A$1:$A,$J$4))

    Alternatively, you could use the =QUERY function if you wanted something more scalable to large groups of companies.

    =QUERY({'All Report'!$A$1:A,'All Report'!$B$1:$B},"select count(Col1) where (Col1 matches '"&JOIN("|",{$J$2:$J$4})&"') and Col2='"&$A3&"' label count(Col1) ''")

    Explanation

    The way you currently have your =COUNTIFS function set up, it is attempting to compare each cell of column A of your All Report sheet to an array. Therefore, you never get a match because no individual cell will match the full J2:J4 array.

    The first solution I provide simply completes three =COUNTIFS—one on each company in the group—and sums their results together.

    The second solution uses the =QUERY function to get a count of the number of rows that match certain criteria. The first of those criteria uses a regex search to check if any of the companies in the group are present in column A. The second criteria checks if the specified caseworker is in the same row but in column B. Together, this means that the query will count each time a specified caseworker appears with any of the specified companies.

    Functions used: