Hi, I am trying to calculate a running “this month’s expense” value based on my transactions sheet. Ideally, the number would reset on the 1st of each month. Does anyone know how to do this?
@hbwilliams22, I’m curious why you want to build this custom instead of just reviewing the Monthly Budget dashboard. It has a rollup running expense value that updates as you categorize and when you switch months it starts over.
Hi Heather, I’m attempting to create a “mobile friendly” tab for viewing on my iPhone.
Also, I do not use the “Monthly Budget” dashboard as of right now because I do not define monthly budgets per individual category. Is there a way for me to define a total monthly budget number vs. assigning a budget to each category?
Hi @hbwilliams22,
Not sure if you still need help here. Sorry I dropped the 
on that one.
I like where you’re going with this, hope it’s worked out (or will work out) and maybe you can share in an upcoming Winning Workflow we’ll be launching soon 
I don’t know if it helps, but the How to use the Spending Money budget spreadsheet is probably a good starter sheet to review the formulas for something like this. It basically calculates it this way I think. It’s also supposed to be set up to be more mobile friendly.
Hi @hbwilliams22,
Here’s a way you can calculate This Month’s total expenses with a single formula that won’t need to be adjusted every month. It looks complicated but there’s a reason for it.
The basic concept is to sum all the amounts in your Transactions sheet where:
- the date of the Transaction, when converted to the date of the end of the last month plus one day equals the first day of the current month
AND - the category of the Transactions is an “Expense” type"
So if a transaction is dated March 15, 2020, the date at the end of the last month is Feb 29, 2020. Plus one day equals March 1, 2020. That matches the first date of the month if the current month is March 2020. This transaction would be summed up if its March 2020 but not any other month.
Here’s the formula
=SUMIFS(Transactions!D2:D,{ARRAYFORMULA(EOMONTH(Transactions!A2:A,-1)+1)},DATE(YEAR(TODAY()),MONTH(TODAY()),1),ARRAYFORMULA(VLOOKUP(Transactions!C2:C,{Categories!A2:A,Categories!E2:E},2,FALSE)),"Expense")
In this example:
- Transactions!D2:D is the Amount column
- Transactions!A2:A is the Date column
- Transactions!C2:C is the Category column
- Categories!A2:A is the Category column
- Categories!E2:E is the Type column
Adjust the formula to match your columns if they don’t match the above formula.
If you want to get the Monthly Income total, change Expense to Income.
This formula could also be modified to get the total for a single category for the current month.