How can I use a Google Spreadsheet query to sum rows in another sheet

Question

I have a Google Spreadsheet that spans 3 (work)Sheets. On the first page I have a set of rows with customer data.

That looks like

C      D    E
Joe    -    100
Bob    -    200
Joe    -    300
Jane   -    50

And on sheet 2 I have a column with a formula that attempts to query those values and sum the purchase values for each customer (col E).

The formula I tried to use which fails is:

"=(QUERY('data'!B40:E60, "Select C, Sum(E) where C contains """&A18&""" Pivot C"))

In all seriousness though I really don't care about selecting C this is just the 20th variation I tried. What I am trying to do is sum E for all rows where C matches the cell A18 which is the customer name on sheet 2.

JPV JPV · Accepted Answer · 2014-12-23T17:01:41

Try:

=QUERY('data'!B40:E60, "Select C, Sum(E) where C = '"&A18&"' group by C")

and see if that works ?

How can I use a Google Spreadsheet query to sum rows in another sheet

1 Answers