Monthly Calculation

Jason Voorhies

I need to calculate monthly expenses but I've managed to stump myself on the formula/function. Here is my use case: I've got individual line items by user, but I want to sum their monthly expense by month. I have a range the individuals are contracted with us...say from 7/1/2022 to 5/31/23. How could I write the formula to look at that date range and pull out a specific month AND calculate the spend (if the hourly rate is known)?

Thank you in advance!

Jason

ShelbyWarren

@Jason Voorhies Here's an example:

Check out the formulas in the Expense Month column and the Monthly Sum column and see if that helps you apply them to your use case. I've included the formulas below:

• Expense Month: =IF([Expense Date]@row = "", "", MONTH([Expense Date]@row))
• Monthly Expense Totals: =SUMIF([Expense Month]:[Expense Month], [Month #]@row, [Expense Amount]:[Expense Amount])

Hollie Green

You will need a column with either the specific date or the month the expense occurred in to pull out by month. It depends on how your sheet is set up

I would use a Sumif formula and multiply by the hourly rate

Jason Voorhies

@ShelbyWarren Thank you so much!! This really helped!

ShelbyWarren

@Jason Voorhies Glad it helped. Feel free to reach out if you have other solutions you're working through.