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:
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.