Forum Discussion
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 want the solution in automatic manner in which i don't need to increase/decrease any range)
Thanks in advance 🙂
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.
7 Replies
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.The same idea as you except that I used DROP to return the range
= SUM( DROP( amountA,, XMATCH(1, MONTH(dateA),,-1) - 1 ) )
Hi, for automating YTD calculations that always begin from January regardless of the cell placement, you might consider using dynamic array formulas or a helper column with month extraction logic. Also, if you're dealing with academic metrics and need an efficient way to compute your semester scores, the Student GPA Calculator offers a reliable method. It’s a great tool to streamline academic evaluations. Hope this helps!
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)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.
