Google Sheets COUNTIFS Range of cells as criteria possible?

Question

In the Google Sheets Formula =Countifs, is it possible to have a range as a criterion, for example:

=COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C)

I couldn't find a solution to this, is there any way of doing this. Basically, I need to compare a filter against a list of names in a column in the second criteria.

I tried doing it with the example in my question (=COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C)) but it doesn't work.

I have a sheet where issues with tickets are entered "ExampleSheet". This sheet contains the name of the employee, the issue and other details. In another sheet, I have a list of teams and their team members (List_of_Names), for example, team A and its members are all in column E.

In a sheet "Analysis" I am trying to filter by teams. In this sheet, all issues are referenced automatically, and next to them I want to count the instances for members or a team.

The formula should tell me how many instances of issue x has arisen within team E (by all its members). If the team had only one member, then it would be very easy:

=COUNTIFS(ExampleSheet!$H$2:$H; J14; ExampleSheet!$A$2:$A; ListOfNames!E2)

But as there is more than one member in a team, the "ListOfNames!E2" should be "ListOfNames!E2:E". This does however not work.

here is a link to an examplary spreadsheet:

https://docs.google.com/spreadsheets/d/1UtSPzk0BtcC182tV0Ev58cHcmhs247zKu7fTGYZSh_c/edit?usp=sharing

The first sheet ("Entries") is where all the entries of issues with agent tickets are made. The second sheet ("Analysis") is supposed to be a tool for filtering The last sheet ("Teams") includes the team definitions.

I am trying to work on the formula in column L6:L and I6:I in the second sheet. For now, I have manually typed in the names of team members in the formula. This is however not what I want, as the teams can change quite often and I do not want to update the formula each time. I would rather have the formula read out the names from the cell ranges in the third sheet.

Answer 1

here is a link to an examplary spreadsheet:

https://docs.google.com/spreadsheets/d/1UtSPzk0BtcC182tV0Ev58cHcmhs247zKu7fTGYZSh_c/edit?usp=sharing

The first sheet ("Entries") is where all the entries of issues with agent tickets are made. The second sheet ("Analysis") is supposed to be a tool for filtering The last sheet ("Teams") includes the team definitions.

I am trying to work on the formula in column L6:L and I6:I in the second sheet. For now, I have manually typed in the names of team members in the formula. This is however not what I want, as the teams can change quite often and I do not want to update the formula each time. I would rather have the formula read out the names from the cell ranges in the third sheet.

Thanks

Answer 2

=ARRAYFORMULA(IF(H6:H<>""; IF($K$3="Alle"; COUNTIF(Entries!$G$2:$G; H6:H); 
 IF($K$3="TEAM C"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!E2:E})); 
 IF($K$3="TEAM A"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!A2:A})); 
 IF($K$3="TEAM B"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!C2:C})); 
 IF($K$3="TEAM D"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!G2:G})); 
 COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; $K$3)))))); ""))

Answer 3

ARRAYFORMULA(IF(H6:H<>""; IF($K$3="Alle"; COUNTIF(Entries!$G$2:$G; H6:H); IF($K$3="TEAM C"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!E2:E})); IF($K$3="TEAM A"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!A2:A})); IF($K$3="TEAM B"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!C2:C})); IF($K$3="TEAM D"; SUM(COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; {Analysis!G2:G})); COUNTIFS(Entries!$G$2:$G; H6:H; Entries!$A$2:$A; $K$3)))))); ""))

This seems well for individuals, but it does not seem to be working if you select a team...