Apologies for a very newbie question, but I am just starting to explore the wonderful world of Google Query in my Tiller spreadsheet. I was attempting to use GROUP BY to sum the total value of transactions by Month, but I keep getting a #Value! error and I have no idea what I’m doing wrong. Can anyone help?
Here’s the formula I’m using:
=query(Transactions!A1:U9172, “SELECT Col2, Col3, Col4, Col5, Col6, Col12, sum(Col7) WHERE Col7 is not null GROUP BY Col12”,-1)
where
Transactions!A1:U9172 = my transactions database (will need to make this reference dynamic later)
Col2 = Date
Col3 = Description
Col4 = Category
Col5 = Group
Col6 = Tags
Col12 = Month
Col7 = Amount
I’ve been through an bunch of online YouTube videos at this point and can’t figure out what I’m doing wrong. I can filter using WHERE for Col12 = date ‘2025-01-01’ but if I try to SUM(Col7) I get the #Value! error. What am I doing wrong with SUM? Any help would be deeply deeply appreciated!
Try changing your SELECT to: SELECT Col12, sum(Col7)
Once you aggregate and group by Month, the other non-aggregated columns cannot be displayed.
I believe your QUERY result will be a monthly cashflow type value, assuming transfers within the month all cancel to zero. If you want monthly expenses, then consider adding a Category Type to your Transactions sheet to filter by.
Look what I made! : ) (It’s total spend by category per month across the last 3 years.) I’m a bit limited by the inability of Google Sheets to make very complex graphs but I’m pretty proud of myself. Thank you so much for your help with the Query function!
