I've done some research but it wasn't satisfactory so I hope you can help me out here.
I have a table for my expenses which I typed manually inro Google Sheets:
| Date | Name | Category | Debits |
|---|---|---|---|
| 7/18/2022 | Trader Joe's | Groceries | $47.06 |
| 7/23/2022 | Hola Tacos | Restaurants | $80.00 |
| 7/24/2022 | Eversourse | Utilities | $36.00 |
| 8/24/2022 | New Tires | Transportation | $53.00 |
| 8/29/2022 | New Deli | Restaurants | $80.00 |
| 8/26/2022 | Costco | Groceries | $93.00 |
| 9/31/2022 | Tooth Paste | Wellness | $100.00 |
I'm trying to calculate the sum of all expenses for each month using this QUERY() function:
=QUERY($A$2:$D, CONCATENATE("select SUM(D) Where NOT A is Null AND MONTH(A) 1=",MONTH(F5)), 0)
where I typed the value into cell F5 manually.
So I'm trying to get something like this ('July 2022' is cell 'F5'):
| July 2022 | August 2022 | September 2022 |
|---|---|---|
| $163.00 | $226.00 | $65.00 |
, but the QUERY() function's output looks like this:
| July 2022 | August 2022 | September 2022 |
|---|---|---|
| sum | sum | sum |
| $163.00 | $226.00 | $65.00 |
Problem 1: What I want is to avoid this new 'sum' row. So I tried to fix it with a label SUM(D) '' inside the query so it would look like this:
=QUERY($A$2:$D, CONCATENATE("select SUM(D) label SUM(D) '' Where NOT A is Null AND MONTH(A) 1=",MONTH(F5)), 0)
but it gives me a #VALUE! error:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "where" "Where "" at line 1, column 31. Was expecting one of: "format" ... "options" ... "," ...
Problem 2: I have an issue with the month of September, the sum for it specifically is empty although I have values for this month in my table in the Debits column.
Problem 3: How can I calculate the sum of expenses per month for each category that exists in m this table?
Here's my document available online. Please, let me know how I can fix these issues.
problem #3
the pivot table will be a good use for that
