Google-sheets – Remove header and blank row from the results of a SUM, GROUP BY query

google sheetsgoogle-sheets-query

I have a two column table where one column is some sort of key where duplicates are allowed and the other column includes amounts.

I then have the following formula somewhere else on the sheet:

=QUERY(A1:B, "SELECT SUM(A) GROUP BY B")

The results of that query begin with 'sum', then a blank line, then the summed values:

spreadsheet with sum

How can I modify the existing formula so that I do not have the 'sum' header and the blank line (if I can at all)? I want to avoid using multiple cells to create the desired affect and I want to avoid scripting.

Best Answer

To suppress the header, end the query string with label sum(A) ''.

The blank spot below the header comes up because the column B, besides the letters a,b,c, also contains a bunch of blank cells, which form a group of their own. Since you don't want them, add the clause where B<>''.

End result: 

=query(A1:B, "select sum(A) where B<>'' group by B label sum(A) ''")

(Case doesn't matter: I prefer lowercase because more text fits into the same horizontal space.)

Related Topic