How to Use SUMIFS to Match Category and Month Automatically in Excel
Question details
The user wants to dynamically sum transaction amounts based on specific categories and specific months or years.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking financial transactions or analyzing data where new records are added continuously, requiring automated calculation updates.
- Observed behavior
- A dynamic formula is needed that automatically incorporates new data into the total sum without requiring manual range expansions every time new rows are appended.
Ensure your dataset is organized with clear column headers (such as Category, Date, and Amount) and that the Date column contains valid numerical date values rather than plain text.
Use an Excel Table and SUMIFS with Date Boundaries
By converting your dataset into an Excel Table, the data ranges will dynamically expand. Using SUMIFS alongside EOMONTH allows you to filter transactions accurately by month and year without manually updating the formula.
Structured references in Excel Tables automatically expand when you add new data. Combining this feature with the SUMIFS function ensures that your totals stay up to date.
Because the SUMIFS function cannot process functions like MONTH() directly on a range, we use the EOMONTH function to calculate the first and last days of the target month as criteria.
Highlight your transaction data including the headers. Go to the Insert tab on the ribbon and click Table (or press Ctrl + T). Ensure 'My table has headers' is checked, click OK, and name your table 'Table' in the Table Design tab.
Designate specific cells outside your table for your filters. For example, type your target category into cell B12, and a valid reference date (like the first day of the target month) into cell B2.
Select the cell where you want the total to appear and input the formula: =SUMIFS(Table[Amount], Table[Category], B12, Table[Date], ">="&EOMONTH($B$2,-1)+1, Table[Date], "<="&EOMONTH($B$2,0))
Press Enter to calculate the sum. Add a new row of data to the bottom of your Excel Table to verify that the formula dynamically includes the new transaction amount automatically.

Easily Calculate Dynamic Sums with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced formulas like SUMIFS and EOMONTH, as well as dynamic Table structured references. You can seamlessly manage and analyze your financial data or transaction logs without purchasing expensive software.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your transaction workbook in WPS Spreadsheet.
- 2. Create a dynamic table: Select your data range, navigate to the Insert tab, and click Table. This action allows your formula ranges to expand automatically as data grows.
- 3. Input the SUMIFS formula: Type the exact same =SUMIFS() formula using structured references and EOMONTH to set the start and end dates of the target month.
- 4. Analyze your results: Press Enter. Your dynamic sum will calculate instantly and update in real-time as you add new records to the bottom of your table.

Frequently Asked Questions
Why is my SUMIFS formula returning zero even though there is matching data?
This usually happens if the dates in your table are formatted as text instead of actual date values, or if there are hidden spaces in your category names. Use the VALUE function or the Text to Columns tool to convert text dates into real serial dates.
Can I use the MONTH() function directly inside the SUMIFS criteria range?
No, SUMIFS requires a valid cell range and cannot evaluate array operations like MONTH(range) directly. This is why using ">=" and "<=" with EOMONTH boundaries is the standard and most efficient workaround.
How do I sum data across an entire year instead of a specific month?
To sum by year, adjust the date boundaries in your SUMIFS formula to capture the first and last day of the year instead of referencing EOMONTH. For example, use ">="&DATE(2023,1,1) and "<="&DATE(2023,12,31) as your criteria.
What happens to the formula if I rename my Excel Table or its columns?
Excel and WPS Spreadsheet automatically update the structured references in your existing formulas to reflect the new table and column names, ensuring your SUMIFS calculations remain intact without manual editing.




