CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Solana_Station
Solana_Station

Reputation: 311

QUERY function not properly counting matching values with OR criteria in Google Sheets

[Goal] I'm trying to count the number of tickets per employee that has a status with either "Finished," "Finished (Scope)," or "Routed (Sales)." The data that I'm pulling from to count the tickets has the following column names.

Column A: Ticket ID, Column B: Status, Column C: Employee, Column D: Date

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

[Current Formula]

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

[Issue] I was able to create a formula that counts the number of tickets per employee with the status called "Finished," however for some reason, it's not counting tickets that has the status name "Finished (Scope)" or "Routed (Sales)." Can someone please help?

Upvotes: 1

Views: 158

Answers (1)

player0
player0

Reputation: 1

its a regex so you may need to escape brackets. try:

=QUERY('Data'!$A$3:$F,
 "select C,count(A)
  where lower(B) matches 'finished \(scope\)|routed \(sales\)|finished'
    and D >= "&H1&" 
  group by C
  label count(A) 'Total Tickets'", 0)

Upvotes: 2

Related Questions