QUERY Function: How to count with multiple counting criterias

Question

[Goal] I'm trying to count the number of tickets per employee in one column that has a status with either "Finished," "Finished (Scope)," or "Routed (Sales)" for a specific week. In another column I also want to count the number of tickets for a specific week without criteria. The data that I'm pulling from to count the tickets has the following column names.

Column A: Date, Column B: Ticket ID, Column E: Employee, Column H: Finished Week Column K: Week

In the formula, you'll notice that it's referring to cell H1, which the cell contains the current week which is this formula: =TODAY()-MOD(TODAY()-2,7)-1

[Current Formula]

=QUERY('Data'!$A$3:$J,"Select E,
 COUNT(B) where D matches 'Finished|Finished \(Scope\)|Routed \(Sales\)'
 AND H = "&H1&" GROUP BY E LABEL COUNT(B) 'Total Finished Tickets'",0)

[What it should look like] I've created a sample spreadsheet that you can refer to. Link: https://docs.google.com/spreadsheets/d/1MQLgt_SSbUIKv1rEwx-Y21hooxNOOgcUm_j1rFehHdg/edit?usp=sharing

[Issue] I was able to create a table that counts the number of tickets per employee with the status as "Finished" OR "Finished (Scope)" OR "Routed (Sales)." Which is the "Current Result" table (Link: https://docs.google.com/spreadsheets/d/1MQLgt_SSbUIKv1rEwx-Y21hooxNOOgcUm_j1rFehHdg/edit#gid=0).

However, as I tried to add another count criteria, it gave me errors and I don't understand how to properly make this work. I wanted to look like the table of the title "Ideal Result" in the shared link. Can someone please help?

Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. — doubleunary
Can you share a copy of your sheet with a small sample of data in the correct columns, replacing actual names with Name1, Name2, Name3 etc — Aresvik
Hi everyone, many thanks for responding to my questions. I have created a sample sheet (link: docs.google.com/spreadsheets/d/…). — John Tashiro

doubleunary doubleunary · Accepted Answer · 2021-05-09T09:47:23

You can use the pivot clause to get a breakdown by the Status column like this:

=query(
  Data!A3:J, 
  "select E, count(E) 
   where H = " & E4 & " 
   group by E 
   pivot D 
   label E 'Employee' ", 
  0 
)

The downside is that the grand total must then be calculated separately, but that can be done with a simple sum() formula.

Alternatively, get the totals first, and then do a lookup to get the number of finished tickets, like this:

=query(
  Data!A2:J, 
  "select E, count(D) 
   where H = " & E4 & " 
   group by E 
   label E 'Employee', count(D) 'Total new tickets' ", 
  0 
)

=arrayformula( 
  iferror( 
    vlookup( 
      E12:E, 
      query(
        Data!A2:J, 
        "select E, count(D) 
         where H = " & E4 & " 
         and (D = 'Finished' or D = 'Finished (Scope)')
         group by E 
         label count(D) 'Finished tickets' ", 
        1 
      ), 
      2, 
      false 
    ) 
  ) 
)

Note that this serves just to illustrate how to aggregate the data into a report. Your question leaves it unclear as to which status values should be counted for each type of aggregation. No rows with status Routed (Sales) appear in the data, and I cannot see how the expected results you show could be derived from the data.

See your sample spreadsheet.

H1, which the cell contains the current week which is this formula

=TODAY()-MOD(TODAY()-2,7)-1

You may want to try the weeknum() function.

QUERY Function: How to count with multiple counting criterias

2 Answers