Forum Discussion

YashR1994's avatar
YashR1994
Copper Contributor
Jan 22, 2022
Solved

YTD Dynamic Calculation

Hi,   I want a help to calculate YTD dynamically in which it should always start calculate from January month only no matter January month falls in which cell or column in a provided range (here i ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 22, 2022

    YashR1994 

    1) Make sure that the cell with the formula is not formatted as text.

    2) If you use comma as decimal separator, use semicolon in the formula:

     

    =SUM(INDEX(B2:M2;MATCH(1;MONTH(B1:M1);0)):M2)

     

    3) Try confirming the formula with Ctrl+Shift+Enter.

SnowMan55's avatar
SnowMan55
Bronze Contributor
Jun 05, 2025

For those who might be interested in the latest YTD total (or average, etc.) for a period covering more than 12 months (including running YTD totals, averages, etc.), a slightly different formula will be useful:

=SUM(INDEX( B2:M2, XMATCH(1,MONTH(B1:M1),0,-1) ):M2)

(The included spaces are optional.  The XMATCH function requires Excel 2021 or a later version.)  In some cases it will be necessary to fix (prepend a $ to) the row number or column letter of the starting cells.
See the attached workbook for examples.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 06, 2025

The same idea as you except that I used DROP to return the range

= SUM(
    DROP(
      amountA,,
      XMATCH(1, MONTH(dateA),,-1) - 1
    )
  )

 

Resources