3

In Google Sheets, I have the following data:

    |  A  |  B  
  1 | foo |  20
  2 | bar |  50
  3 | baz | -40
  5 | bar | -50
  4 | foo | 100
  6 | baz |  60

Currenly, I'm using this query formula: =QUERY(index(A2:B1000);"select A, sum(B) where A is not null and B is not null group by A order by sum(B) desc";)

But it outputs values, where the sum of B is equal to 0.

So desired output would be:

    |  A  |  B  
  1 | foo | 120
  2 | baz |  20

How can I achieve that?

Also, is there a better way than A2:B1000 for selecting the whole range (except the first row)?

1 Answer 1

3

Main point

You can't filter by the sum within the query that forms that sum. But you can run another query on the output of the first one. Like this:

=query(query(....), "select * where Col2 <> 0")

Other remarks

  • I don't see the need for index command here.
  • Instead of A2:B1000, use A2:B to capture all rows to the bottom of the sheet.
  • It is better to include the header row in the queried range, designating it as a header with the third parameter (number of queried rows). That is, query(A1:B, "select...", 1) is preferable to query(A2:B, "select..."). When you don't provide the third parameter, the command guesses whether the top row is header or data; and it will sometimes guess wrong.
  • I don't think you need "and B is not null", as null values will be ignored by the sum anyway.

So, my version of the command is

=query(query(A1:B, "select A, sum(B) where A is not null group by A order by sum(B) desc", 1), "select * where Col2 <> 0", 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.