SOLVED

YTD Dynamic Calculation

New Contributor

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 want the solution in automatic manner in which i don't need to increase/decrease any range)

 

YashR1994_1-1642862385869.png

 

Thanks in advance

 

 

4 Replies

@YashR1994 

Let's say that the months are in B1:M1, and the numbers in B2:M2.

The YTD is given by

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

@Hans Vogelaar 

 

its giving me an error.

 

YashR1994_0-1642864999661.png

 

best response confirmed by YashR1994 (New Contributor)
Solution

@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.

@Hans Vogelaar 

 

Thanks its now working for me