How to Use SUMIFS to Calculate Current Month Expenses in Excel
Question details
The user wants to use a SUMIFS formula to sum expenses that occurred specifically within the current month by establishing date boundaries.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking monthly budgets or categorizing financial records to determine the total expenses within a specific calendar month.
- Observed behavior
- Needs to dynamically sum specific transaction amounts using a start date (first day of the month) and an end date (first day of the following month) without manually updating the entire formula.
Ensure your expense data is formatted as a formal Table and verify that all entries in your Date column are recognized as valid serial dates rather than plain text.
Apply SUMIFS with Date Criteria and the EDATE Function
Calculate monthly expenses by setting the start of the month as the lower limit and using the EDATE function to dynamically determine the upper limit.
The SUMIFS function can evaluate multiple criteria, making it ideal for date ranges. By combining it with the EDATE function, you can automatically establish the first day of the following month as your upper boundary, ensuring that all days within the current month are accounted for regardless of whether the month has 28, 30, or 31 days.
Select your expense dataset and navigate to the 'Insert' tab, then click 'Table'. Name this table 'Table1' in the Table Design tab. Ensure it includes columns named 'Amount', 'Description', and 'Date'.
In an empty cell (e.g., E3), type the first day of the current month (like '10/1/2023'). In another cell (e.g., M7), type the specific expense category you want to sum, such as 'Subscriptions' or 'Utilities'.
Select the cell where you want the total to appear (e.g., O7) and input the following formula: =SUMIFS(Table1[Amount], Table1[Description], M7, Table1[Date], ">="&E3, Table1[Date], "<"&EDATE($E$3,1))
Press Enter to execute the formula. The EDATE function dynamically finds the exact date one month ahead of E3, and the strictly less-than operator ("<") prevents the first day of the next month from being included in the sum.
Calculate Monthly Expenses Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like SUMIFS and EDATE, allowing you to track and manage your monthly financial data with complete accuracy.
- 1. Open your Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your monthly expense data.
- 2. Verify Data Formats: Select your date column, right-click, choose 'Format Cells', and confirm they are set to 'Date' rather than 'Text'.
- 3. Apply the Formula: Click into your desired total cell and paste the SUMIFS and EDATE formula to calculate the monthly sum.
- 4. Auto-Fill for Other Categories: Drag the fill handle downwards to quickly apply the same formula logic to other expense categories in your summary table.
Frequently Asked Questions
Why is my SUMIFS date formula returning an error or zero?
This usually happens if the dates in your source column are formatted as text rather than actual serial dates. You can use the 'Text to Columns' tool under the Data tab or the DATEVALUE function to convert them into real spreadsheet dates.
How does the EDATE function work in this formula?
EDATE returns a date that is a specified number of months before or after a starting date. In the formula EDATE($E$3,1), the '1' tells the software to calculate exactly one month after the date in cell E3, serving as the upper boundary for the current month.
Can I use TODAY() instead of a cell reference to always get the current month?
Yes. You can replace the start date criteria with EOMONTH(TODAY(),-1)+1 to automatically find the first day of the current month, and use EOMONTH(TODAY(),0)+1 to find the first day of the next month, making your formula entirely self-updating.
