Formula Errors

How to Use SUMIFS to Match Category and Month Automatically in Excel

Camila MilosovichCamila Milosovich Nov 11, 2026 869 views

Question details

The user wants to dynamically sum transaction amounts based on specific categories and specific months or years.

How to Use SUMIFS to Match Category and Month Automatically in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Convert your dataset to an Excel Table

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.

2
Set up your criteria cells

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.

3
Enter the dynamic SUMIFS formula

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))

4
Apply and test the formula

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.

Use an Excel Table and SUMIFS with Date Boundaries
Adjusting Cell References: Make sure to adjust 'Table[Amount]', 'Table[Category]', and 'Table[Date]' to match the actual names of your table and columns.
Free Spreadsheet Software

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. 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your transaction workbook in WPS Spreadsheet.
  2. 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. 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. 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.
Fully compatible with Microsoft Excel formulas, functions, and file formats.Supports dynamic tables that update automatically when new data is added.Free, lightweight, and features a familiar tabbed interface for seamless migration.
microsoft office alternative - wps office

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.