Link to Copy sheet to see what I'm going fior roughly https://docs.google.com/spreadsheets/d/1R9bwQwef_Qp4Lidq3Zuz9ETWot-fA86Tzh71I7P9PtQ/edit?usp=drivesdk
CodePudding user response：
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: