Google Sheets can't find Col4 using Query

Question

I made a budget spreadsheet that automatically updates from a form I fill out. I'm trying to use query to sum specific cells since sumif doesn't automatically include new entries and query would be easier to change each month.

Column A is hidden since it just contains the timestamp information. Column B is the date that I made the purchase. Column C is the category of the purchase. Column D is the amount I spent.

And here is the query formula I'm using to try to add all income together:

=query(C152:D184,"Select Sum (Col4) where Col3='Income'")

But I get the error "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col4". Any advice?

Answer 1

You need to enclose a real range C152:D184 in {} to use ColN (you'll have Col1 and Col2 here). Otherwise use letters (C and D). Also you are missing GROUP BY statement and LABEL (this one not to display a header for the sum).

So this:

=QUERY({C152:D184}, "SELECT SUM(Col2) WHERE Col1 = 'Income' GROUP BY Col1 LABEL SUM(Col2) ''",)

Or this:

=QUERY(C152:D184, "SELECT SUM(D) WHERE C = 'Income' GROUP BY C LABEL SUM(D) ''",)

Or this:

=SUMIF(C152:C184, "Income", D152:D184)

Or this:

=SUM(FILTER(C152:C184, D152:D184 = "Income"))