Function Problems

How to Use SUMIFS to Sum Excel Values by Month and Year

Steven Parker Oct 16, 2026 871 views

Question details

Calculate the total sum of values for a specific month and year dynamically without manually typing start and end dates.

How to Use SUMIFS to Sum Excel Values by Month and Year

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic monthly reports or dashboards where data needs to be aggregated based on a user-defined target month cell.
Observed behavior: The user seeks a dynamic formula method to sum values falling between the first and last days of a specified month and year.

Before you start

Ensure your date column contains valid Excel date serial numbers, not just text strings. The target month cell used for your criteria should also be formatted as a valid date, typically the first day of the target month.

Solution 1Recommended

Use SUMIFS combined with the EOMONTH Function

By pairing SUMIFS with EOMONTH, you can automatically calculate the upper and lower boundaries for any given month to sum data dynamically.

The EOMONTH function calculates the last day of the month for a specified date. Using this alongside the SUMIFS function creates a dynamic boundary from the first day to the final day of your target month, eliminating the need to type exact dates manually.

Set up a target date cell

Select a cell (e.g., D2) and enter your target month and year as a valid date. For example, type '1/1/2023' to represent January 2023.

Select the result cell

Click on the cell where you want the total sum for that month to appear.

Enter the SUMIFS formula

Type the formula: =SUMIFS(Table1[Units], Table1[Date], ">="&D2, Table1[Date], "<="&EOMONTH(D2,0))

Calculate the result

Press Enter. The formula will automatically sum all the values in the 'Units' column that fall between the date in D2 and the last day of that exact month.

Use SUMIFS combined with the EOMONTH Function

Understanding the EOMONTH parameter: The '0' at the end of the EOMONTH(D2, 0) formula tells Excel to return the last day of the current month. If you needed to sum data for the previous month, you could change it to -1.

Easily Sum Data by Month and Year in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like SUMIFS and EOMONTH, allowing you to build dynamic monthly reports effortlessly. Enjoy a familiar interface and comprehensive data analysis tools for free.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your date and unit columns.
2. Select your output cell: Click the cell where you want to calculate the monthly total.
3. Input the dynamic formula: Type =SUMIFS(Units_Range, Date_Range, ">="&Target_Date, Date_Range, "<="&EOMONTH(Target_Date,0)) and press Enter.
4. Format your target date: Right-click your target date cell, choose 'Format Cells', and select a custom format (like 'mmm-yyyy') to cleanly display the target month.

Fully compatible with Microsoft Excel formulas, formatting, and file typesFree and lightweight alternative for powerful spreadsheet data analysisBuilt-in templates for monthly budgeting and trackingSeamless and familiar interface making formula writing simple

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS formula returning a zero?

This usually happens if your dates are formatted as text instead of valid date values. Select your date column, go to Data > Text to Columns, and click Finish to convert them into proper dates that SUMIFS can recognize.

Can I sum values by year without specifying a month?

Yes. You can use a similar SUMIFS formula but adjust the boundaries using the DATE function. For example, to sum for the year specified in cell D2: =SUMIFS(Units, Dates, ">="&DATE(D2,1,1), Dates, "<="&DATE(D2,12,31)).

Does EOMONTH account for leap years?

Yes, the EOMONTH function automatically adjusts for leap years in Excel, returning February 29th for a leap year and February 28th for a standard year.

Excel & Data Problems