Sum my monthly expenses based on category

Question

I would like to group my expenses in each month based on each category

One sheet I have columns: billing month, post date, transaction date, description, amount and category

Another sheet I have columns: billing month, category, amount

I tried but still does not work:

=IFERROR(QUERY(Expense_U!$B:$F, "select sum(E) where F = '"&B2&"' and month(A) + 1 matches '"&MONTH(A2&1)&"' group by C label sum(D)''", 0), 0)

=SUMIF(Expense_U!D$2:D,MATCH(($B2=Expense_U!$E$2:$E)*(A2=Expense_U!A2),0))

Here is the link to my sheet https://docs.google.com/spreadsheets/d/1mN-_93YPaV975IPMzfBECTmXxpPDI70dnJHhvcVGFz8/edit?usp=sharing

Thank you for your help!

Answer 1

You have two options, a Pivot Table or using the QUERY() function.

Pivot Table

Go to the Insert > Pivot Table. For your Data Range, select your whole data set. Include the headers too. Where you choose to put the pivot table will be up to you.

You'll be shown the Pivot Table Editor. Under Rows, add the Billing Month and the Category in that order. This tells your pivot table to aggregate data with these specific categories. Without it, it'll just display the total. To finish it off, under Values, add Amount. In Filters, add Category and de-select #N/A to tell the Pivot Table ignore them.

Here is the finished product:

But Pivot Tables sometimes do not readjust their data range, so maybe a simpler way and closer to what you were going for would be to use the QUERY() function.

QUERY() Function

The QUERY() function is basically Google Sheets version of SQL. It's limited subset which you can read more on here. Just plug this formula into a blank sheet:

=QUERY(Expense_U!A1:F, "SELECT A, F, SUM(E) WHERE F != '#N/A' AND A IS NOT NULL GROUP BY A, F", 1)

You should arrive at this:

Note that it doesn't have the subtotals and total. You can compute that on your own, maybe even using another query off to the side. This by the way solves the issue of the data range not adjusting to fit the entire data set. Since we are referencing A1:F, we are taking all the cells from A1 to the bottom of column F, and as such it will always contain the entire list. The query itself in the second parameter of the function I won't discuss in too much depth, but if you have any SQL knowledge, it should be mostly readable. Note the use of column letters (i.e. A, F) to refer to columns instead of column headers.