Try this SMART formula to calculate YTD in Excel (works for Financial Years too!)

Easy, quick and smart YTD calculation with Excel formulas.
Learn how to total Year to Date values for either calendar year (Jan to Dec) or financial year (any starting point) with simple Excel formulas.
The video also has a special bonus trick on how to use OFFSET formula when you have tabular data.

⏱In the video 👉
==============
0:00 - Introduction
0:48 - YTD formula for cell range values (for year starting in January)
1:25 - YTD formula for April / July / September start
2:01 - YTD formula for tabular data
3:13 - YTD in tables for other month starts
3:23 - Quick note about Volatile functions like OFFSET

Sample workbook 📗👇
===================

There is a surprise in there too 🎁

Watch next... 📽
============

💥Want more? Consider joining my online class 📚👨‍🏫
============================================

Have a BEAUTIFUL Day 🌞🌼😀

#YTDFormula #Chandoo

[sbabu]

Amazing I was using two tables to get YTD numbers... By your help it was so easy now... Thanks a lot ... Chandoo... 👌👍

[nayanrajgor]

One of my my professor in the last semester of my MBA program recommended me to subscribe to your channel. I think that was a great recommendation.

[lalitkumar]

HI Chandoo, here is a Trick, We don't need offset function just Sum function is enough. Trick is that just hold the Ctrl key while selecting YTD(E4) and
D5 will be glad it change the Table reference to Cell reference and then the formula works !!!Thanks a lot...Keep Teaching us.

[usauniversal]

All dreamers out there don't lose hope this is temporary situations it will pass keep going you are on the way to success don't give up your success will, ✊ ✊ ✊

[usauniversal]

When you believe in yourself you become unstoppable 💪

[frenan]

Hi! Thanks in advance. I use (Sumar.Si.Conjunto) SUMIFS to do it, with some criteria

[gvinodnair]

Chandoo...I'm amazed 😮 to think how many of such cool tricks are in your Excel arsenal!

[sagarpatel]

you are truly AMAZING...sir. i have no words to admire you..thank you so so much for this kind of information.

[sumardjo]

thanks for knowledge
and Sample File
thanks very much
I can follow to step by step

[ahsanbaig]

This is absolutely gold!! Does anyone know how I can use this formula to calculate QTD?

[wayneedmondson]

Hi Chandoo. Great tips! Thanks for sharing both techniques :)) Thumbs up!!

[lisaa]

never used those first 2 elements in an offset before. i used offset often usually to defina a range that might not be in a table. we a plug-in to pull data into excel and it overrides the sheet. so i define range as offset(a1, 0, 0, counta(a:a), counta(1:1)).

[JoseAntonioMorato]

Dear Chandoo,
Using the SCAN function, the data spill is obtained:
=SCAN(0, D5:D27, LAMBDA(x, y, IF(MONTH(OFFSET(y, , -1))<>1, x+y, 0*x+y))) –> YTD (January Start)
=SCAN(0, D5:D27, LAMBDA(x, y, IF(MONTH(OFFSET(y, , -1))<>4, x+y, 0*x+y))) –> YTD (April Start)

In the table, your calculation is better, because, if you want to use the SCAN function, the calculation would have to be done in an external column:
=SCAN(0, data[Registrations], LAMBDA(x, y, IF(MONTH(OFFSET(y, , -1))<>I4, x+y, 0*x+y))) –> YTD TABLE
(Starts according to the month established in cell I4) 🤗

[Reyesnes]

Hi @Chandoo.

Can you make a video please, about this same topic "YTD"? But making it dynamic. Having a dropdown list with the months-year?

For example, we have this table:

| Month | Amount |

| Jan-22 | $123.43 |
| Feb-22 | $154.54 |
| Mar-22 | $21.92 |
| Apr-22 | $143.11 |
| May-22 | $77.00 |
| Jun-22 | $186.98 |
| Jul-22 | $142.52 |
| Aug-22 | $112.57 |
| Sep-22 | $55.57 |
| Oct-22 | $149.70 |
| Nov-22 | $95.30 |
| Dec-22 | $124.52 |
| Jan-23 | $2.36 |
| Feb-23 | $158.92 |
| Mar-23 | $170.61 |
| Apr-23 | $15.61 |
| May-23 | $70.12 |
| Jun-23 | $17.03 |
| Jul-23 | $102.85 |
| Aug-23 | $33.70 |
| Sep-23 | $25.05 |
| Oct-23 | $132.00 |
| Nov-23 | $68.54 |
| Dec-23 | $181.37 |

Now, I want to see the comparative about YTD Aug-23 (from jan-23 to aug-23) vs YTD Aug-22 (from jan-22 to aug-22).
But when I say Dynamic,

But when I say "Dynamic" I mean that there is a cell containing the dropdown list with the end months.

For example if the cell says "Aug-23", it means that it has to calculate the YTD from Jan-23 to Aug-23. If the cell says "Sept-23", then it should calculate the YTD from Jan-23 to Aug-23. That is, it also has to respect the year.

[finnpj]

Thank you, very usefull - but please turn down the background music :-)

[TSSC]

A possible problem in YTD calculations is when the data contains gaps. The methods presented works if periods with no registrations are included with a zero value. That is not always the case.

[DevaGizmo]

Can you explain how to calculate WTD & MTD in the same way
Thanks in advance

[shoaibrehman]

Awesome trick, please turn off or slow down background music.

[bharatthadeshwar]

Hi Chandoo, how would you do the year to date (YTD Cals) if the monthly figures are in columns rather than rows?

[raops]

Amazing Sir, can this be applied for Daily Revenue date as Daily and MTD