CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Stefan Meier
Stefan Meier

Reputation: 19

Filter a Query formula by SUM()

I'm building a sales dashboard for my company.

I use the following QUERY to list all my employees and their revenue:

=QUERY(Master!A2:O, "
SELECT F, SUM(O), SUM(G) 
WHERE A >= date '" & text(D3,"yyyy-MM-dd") & "' 
and A <= date '" & text(D5,"yyyy-MM-dd") & "' 
AND UPPER(F) contains '"&$L3&"' 
AND UPPER(D) contains '"&$L5&"' 
GROUP BY F
ORDER BY SUM(G) DESC
LABEL SUM(G) 'Revenue', SUM(O) 'Work Hours', F 'Employee'
")

Some of the data are not attached to a person and their work hours, so I'd like to get rid of these results. To only List my Employees by Work Hours and Revenue.

Here is what I get out of this formula and on the right what I'd like to have:

enter image description here

I would like to cut out the irelevant Online Sales and the Employees who just worked bellow 10 Work Hours.

I tried bellow "GROUP BY F":

AND SUM(O) > 10

HAVING SUM(O) > 10

WHERE SUM(O) > 10

Somehow if I try to filter a SUM() it get's rejacted..

How can I fix that?

Upvotes: 0

Views: 56

Answers (1)

player0
player0

Reputation: 1

try:

=QUERY(QUERY(Master!A2:O, 
 "select F,sum(O),sum(G) 
  where A >= date '" &TEXT(D3, "e-m-d")&"' 
    and A <= date '" &TEXT(D5, "e-m-d")&"' 
    and upper(F) contains '"&$L3&"' 
    and upper(D) contains '"&$L5&"' 
  group by F
  order by sum(G) desc
  label sum(G)'Revenue',sum(O)'Work Hours',F'Employee'"), 
 "where Col2 > 10 
    and not Col1 contains 'Online Sales'", 1)

Upvotes: 2

Related Questions