1

I've got a sheet where I'd like to sum each column B-D but filtered/grouped by the value in Column A.

If I use a basic query like

=QUERY(B1:N100, "select sum(B) where A = 'whatever'")

then I get a single value out- the values in B, summed.

How do I alter this query so that I get a single value out for each column, B-N?

player0
  • 124,011
  • 12
  • 67
  • 124
ABMagil
  • 4,579
  • 4
  • 21
  • 35
  • I have discovered `SUMIF` but I'm not sure if there's a way to do it with `QUERY` to get all the layout goodness that comes with it – ABMagil Oct 14 '22 at 20:23
  • please share the sample spreadsheet with your desired output and remove sensitive information. – Twilight Oct 14 '22 at 22:46
  • Make sure to add input and expected output as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 15 '22 at 09:34

1 Answers1

3

try:

=QUERY(A1:N100, 
 "select sum(B),sum(C),sum(D),sum(E),sum(F),sum(G),sum(H),sum(I),sum(J),sum(K),sum(L),sum(M),sum(N) 
  where A = 'whatever'")

or:

=QUERY({A1:N100}, 
 "select "&JOIN(",", "sum(Col"&SEQUENCE(COLUMNS(B:N))&")")&" 
  where Col1 = 'whatever'")

or:

=BYCOL(FILTER(B1:N100, A1:A100="whatever"), LAMBDA(x, SUM(x)))
player0
  • 124,011
  • 12
  • 67
  • 124