Calculate Year-to-Date Sales by Available Months in Excel
Question details
Calculate 2023 and 2024 year-to-date sales using the number of monthly columns currently available in the 2024 data range, without manually editing SUM ranges each month.

- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- The worksheet compares annual sales totals for 2023 and 2024 as new 2024 monthly data is added.
- Observed behavior
- The calculation needs to include only the currently available 2024 months and automatically extend the comparison to the corresponding 2023 months.
Make sure the YTD_2023 and YTD_2024 ranges use the same month-column order and contain numeric sales values. Define YTD_2024 so that it ends at the latest available 2024 month rather than including unused future-month columns.
Use LET, TAKE, MMULT, and HSTACK for dynamic YTD totals
Use this Excel 365 formula when YTD_2024 contains only the month columns currently available and YTD_2023 has matching columns.
The formula counts the available 2024 columns, takes that same number of columns from the 2023 range, calculates totals for each row, and returns 2023 YTD, 2024 YTD, and the difference side by side.
In Excel, open Formulas > Name Manager and confirm that YTD_2023 and YTD_2024 refer to matching sales rows and month columns. YTD_2024 should end with the latest entered month.
Click the top-left cell where the three-column YTD result should spill, leaving empty cells to the right and below for the results.
Enter =LET(cols,COLUMNS(YTD_2024),multiplier,SEQUENCE(cols,,1,0),Total2023,MMULT(TAKE(YTD_2023,,cols),multiplier),Total2024,MMULT(TAKE(YTD_2024,,cols),multiplier),difference,Total2024-Total2023,HSTACK(Total2023,Total2024,difference)) and press Enter.
When a new month is added, update or extend the YTD_2024 reference in Formulas > Name Manager. The formula then includes the new month automatically and takes the matching 2023 month.

Use Excel Tables to reduce monthly range maintenance
Use this approach when your monthly sales data is regularly expanded and you want source ranges to grow with new entries.
Build Automatically Expanding YTD Sales Comparisons in WPS Spreadsheet
WPS Spreadsheet can help you organize monthly sales in matching 2023 and 2024 ranges and calculate year-to-date totals as available months increase. Use named ranges or structured source tables, then apply a dynamic-array formula in a compatible WPS Spreadsheet version to return both totals and their variance.
- 1. Open the sales workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the existing Excel sales workbook from File > Open.
- 2. Align the yearly month columns: Place 2023 and 2024 sales data in matching month-column order, with each row representing the same sales category or person.
- 3. Create or review the YTD ranges: Use Formulas > Name Manager to create or adjust YTD_2023 and YTD_2024 so the 2024 range ends at the latest available month.
- 4. Enter the dynamic calculation: Select an empty output cell, enter the LET formula for the YTD comparison, and press Enter to display the 2023 total, 2024 total, and difference.

Frequently Asked Questions
Why does the formula use COLUMNS(YTD_2024)?
COLUMNS(YTD_2024) returns the number of 2024 month columns currently included in the named range. That count tells TAKE how many matching columns to use from YTD_2023.
What happens when I add another 2024 month?
After YTD_2024 expands to include the new month, the formula recalculates the column count and includes the corresponding additional month from YTD_2023.
Do YTD_2023 and YTD_2024 need to have the same number of columns?
No. YTD_2023 can contain all 12 months, but its first columns must match the month order in YTD_2024. The formula uses only the number of columns available in YTD_2024.
Why does MMULT return an error in my YTD calculation?
MMULT requires compatible numeric arrays. Check that the selected sales cells do not contain text, errors, or mismatched row structures, and ensure the SEQUENCE multiplier matches the number of selected month columns.
Can I reuse this formula for multiple sales sections?
Yes. In Excel, open Formulas > Name Manager and create a LAMBDA name if you need the same calculation repeatedly. Each use must reference 2023 and 2024 ranges with matching row and month layouts.




