Query fails with SELECT A, SUM(B) + SUM(C) GROUP BY A

Question

Given a Google Sheet with the following data:

A	B	C
Jack	150
Jill	10	33
Jill		205

If I enter this function:

=query(A1:C3, "select A, sum(B) + sum(C) group by A", 0)

I get the following in A5:

	sum(sum sum )
Jack
Jill	248

What I expected was:

	sum(sum sum )
Jack	150
Jill	248

Note that both C1 and B3 are empty cells.If I set C1 to 0, it works as expected. Setting B3 to 0 has no effect. I would rather not clutter up my spreadsheet with a bunch of zeros. Any ideas?

Drew · Accepted Answer · 2025-08-22 17:14:36Z

1

Try the following:

=query(arrayformula({A1:A3, n(B1:C3)}), "select Col1, sum(Col2)+sum(Col3) group by Col1 label Col1'Name', sum(Col2)+sum(Col3)'Sum'")

How it works:

The formula recreates the table filled with your data, replacing any blank cells with 0, so you won't need to put in zeroes in your spreadsheet directly. Afterwards, it is used with the query function to essentially sum the total of your values.

Output

Name	Sum
Jack	150
Jill	248

answered Aug 22 at 17:14

Drew

859 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

P.Dow Aug 22 at 22:54

Thanks Drew. The help for the arrayformula function does not describe it very well. Or maybe it's just seeing an example that does something I do understand, that made it understandable :)

Harun24hr · Accepted Answer · 2025-08-23 02:22:24Z

1

You may also try-

=QUERY({A:A,BYROW(B:C,LAMBDA(r,SUM(r)))},"select Col1, sum(Col2) where Col1 <>'' group by Col1",0)

answered Aug 23 at 2:22

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Collectives™ on Stack Overflow

Query fails with SELECT A, SUM(B) + SUM(C) GROUP BY A

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related