CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Solana_Station
Solana_Station

Reputation: 321

QUERY Function: How to count with multiple counting criterias

[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?

Upvotes: 0

Views: 2703

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

To get two independent counts, you can't use a Where clause because that would exclude cases from both counts, but you could use the fact that Query does not count empty cells something like this:

=ArrayFormula(query({if(regexmatch(D3:D,"Finished$|Finished \(Scope\)$|Routed \(Sales\)$"),true,),E3:E,if(K3:K>=H1,true,)},"select Col2,count(Col3),count(Col1) where Col2 is not null group by Col2 label count(Col1) 'Finished', count(Col3) 'New'",1))

enter image description here

Upvotes: 1

doubleunary
doubleunary

Reputation: 19185

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.

Upvotes: 2

Related Questions