5

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.

7

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.)

  • Thanks. That works great. For some reason though, when I did <> '' for certain columns in my real project, I kept getting N/A (no results). The columns I tried were the amount column, a date column, and a derived MONTH column (which is actually how I will be grouping). So I just used a column that will always be a non-empty description. – Robert Hickman Jan 7 '16 at 17:06
  • Try B not null instead of B <> ''. – user79865 Jan 7 '16 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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