Calculation Issues

How to Calculate Year-to-Date Totals Excluding the Current Month in Excel

Nimra Malik Sep 28, 2026 869 views

Question details

The user needs an Excel formula to calculate a year-to-date (YTD) total that sums values up to the end of the previous month, dynamically excluding any data from the current month.

How to Calculate Year-to-Date Totals Excluding the Current Month in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic financial or performance reports where only completed months should be summed for the year-to-date total.
Observed behavior: Requires a dynamic formula to sum values through the final day of the previous month based on a range of dates, ensuring date and value ranges match correctly.

Before you start

Ensure that your date column contains valid Excel dates (not text strings) and that the range of your dates perfectly matches the size of your values range.

Solution 1Recommended

Use SUMIFS and EOMONTH to Calculate YTD Excluding Current Month

This is the most efficient and dynamic way to sum values up to the end of the previous month.

The EOMONTH function combined with TODAY() dynamically identifies the last day of the previous month. The SUMIFS function then adds all values where the corresponding date is less than or equal to that specific cutoff date.

Identify your ranges

Locate your date range (for example, O6:O17) and your corresponding values range (for example, P6:P17).

Select the target cell

Click on the cell where you want the YTD total to appear.

Enter the SUMIFS formula

Type the formula: =SUMIFS(P6:P17,O6:O17,"<="&EOMONTH(TODAY(),-1))

Apply the calculation

Press Enter to apply the formula. The result will dynamically update whenever the current calendar month changes.

Use SUMIFS and EOMONTH to Calculate YTD Excluding Current Month

Format Check: Make sure your date column is formatted as 'Short Date' or 'Long Date' so the SUMIFS function recognizes the values correctly.

Solution 2

Use SUMPRODUCT as an Alternative Solution

An alternative approach using SUMPRODUCT for scenarios where SUMIFS might not be preferred or when dealing with complex array calculations in older versions.

Efficient Data Calculation with WPS Spreadsheet

Calculate Dynamic YTD Totals Easily with WPS Office

WPS Spreadsheet fully supports advanced financial and date functions like SUMIFS, EOMONTH, and TODAY(), making dynamic reporting effortless. It provides a familiar interface and is highly compatible with your existing spreadsheets.

1. Open your file in WPS: Launch WPS Spreadsheet and open your financial or reporting dataset.
2. Insert the formula: Click on an empty cell and type =SUMIFS(values_range, dates_range, "<="&EOMONTH(TODAY(),-1)).
3. Calculate and save: Press Enter to calculate the total, then save your document in .xlsx format to ensure seamless sharing with Microsoft Office users.

100% compatible with Microsoft Excel formulas and .xlsx filesEasily handles SUMIFS and EOMONTH for dynamic YTD calculationsLightweight and fast, even when calculating large datasetsFree to download with a highly familiar user interface

Frequently Asked Questions

Why is my SUMIFS formula returning 0?

This usually happens if your dates are stored as text rather than actual numerical date values. Select your date column, navigate to the Data tab, and use the 'Text to Columns' feature to convert them into valid Excel dates.

How can I include the current month in the YTD total?

To include the current month in your calculation, simply change the EOMONTH parameter from -1 to 0. The updated formula would be: =SUMIFS(P6:P17,O6:O17,"<="&EOMONTH(TODAY(),0)).

Does this formula restrict the sum to the current year only?

The basic formula sums all dates prior to the end of the previous month, regardless of the year. To restrict it strictly to the current year, add another criteria to your SUMIFS function: ">="&DATE(YEAR(TODAY()),1,1).

Excel & Data Problems