How to Calculate YTD (Year-to-Date) in Excel [8 simple ways]

Microsoft Excel has provided a wide range of solutions to calculate YTD(Year-to-Date) under different criteria. In this article, you’ll be able to know all possible & fruitful methods to determine YTD with ease.

Download Excel Workbook

You can download the Practice Workbook that we’ve used to prepare this article.


8 Suitable Approaches to Calculate YTD in Excel

1. Calculating YTD with SUM Functions

Let’s get introduced to our dataset first. Assuming, a business company has sold numbers of products in 12 months of a year & the total selling prices for all months have been recorded. Now we want to know the Year-to-Date Selling Price per unit quality of the products for each month.

📌 Steps:

➤ In Cell E5, type:

=SUM($D$5:D5)/SUM($C$5:C5)

➤ Press Enter & we’ll find the YTD for the first month.

➤ Now use the Fill Handle to fill down the whole column to know other YTD for all months.

calculate ytd with sum function

The formula here is working through the division between two cumulative values for price & quantity. Here the divided part is cumulative prices for the products & the divisor part is cumulative sums of the quantity of the products.


2. Calculating YTD with the Help of SUMPRODUCT Functions

We can find the similar output by using SUMPRODUCT functions too.

📌 Steps:

➤ The SUMPRODUCT formula for our output Cell E5 will be:

=SUMPRODUCT($D$5:D5)/SUMPRODUCT($C$5:C5)

➤ Press Enter & you’ll get the result for the first month.

➤ Now use Fill Handle again to autofill the whole column.

calculate ytd with sumproduct function


3. Determining YTD Growth Rate by Using Dynamic Formula- Combination of SUM, OFFSET & MATCH Functions

Now we’re going to compare sales data between two years for fixed successive months with YTD. We’ll find out the YTD for the months January & February to compare sales data between two years- 2020 & 2021.

📌 Steps:

➤ In Cell I6, type:

=SUM(OFFSET(E5,1,1,1,MATCH($I$5,$F$5:$H$5,0)))/SUM(OFFSET(B5,1,1,1,MATCH($I$5,$C$5:$E$5,0)))-1

➤ Press Enter.

➤ Under the Home ribbon, select Percentage from the drop-down in the Number group of commands. The decimal value you’ve got will be turned into percentage at once.

➤ Now use Fill Handle to autofill other YTD growth rates for all products.

calculate ytd with sum offset match functions


4. Finding out YTD Profits by Incorporating SUM, OFFSET, ROWS & MONTH Functions

We can find YTD values upto a particular month by inputting a specific date too.

📌 Steps:

➤ In Cell I7, our formula for this criterion will be:

=SUM(OFFSET($C$6,ROWS($B$7:B7),0,,MONTH($C$4)))

➤ Press Enter.

➤ Autofill other cells in Column I for all other products by using Fill Handle.

calculate ytd with sum offset rows month functions

Here, our input date is 12 May, 2021. The MONTH function extracts the month from this date & our formula then uses this month number or serial of the month as column number upto which the calculation will be executed finally through OFFSET, SUM & ROWS functions together.


5. Calculating YTD by Using SUMIFS Functions

In this section, we’ll extract year, month & date with the help of YEAR & DATE functions and then apply them to find the YTD rate. In our dataset, selling prices of the products for 10 days have been recorded along with their cost prices. We’ll determine the YTD profit rates for successive 10 days.

📌 Step 1:

➤ In Cell F5, type:

=YEARFRAC(DATE(E5,1,1),B5,1)

➤ Press Enter & with this function, we’ll get the fraction or percentage of the days up to the particular date in Column B based on 365 days in a year.

➤ Use Fill Handle to fill down the whole Column F.

calculate ytd with sumifs functions and helper column

Here YEARFRAC function returns the year fraction representing the number of whole days between start_date & end_date. We’re using the DATE function inside to input the 1st date(1/1/2021) of the year 2021.

📌 Step 2:

➤ Let’s move onto Cell G5 now & type:

=SUMIFS(D:D,E:E,YEAR(B5),F:F,"<="&F5)/SUMIFS(C:C,E:E,YEAR(B5),F:F,"<="&F5)-1

➤ Press Enter & convert it into percentage by selecting Percentage from the drop-down in the Number group of commands under the Home Tab.

➤ Fill down the whole Column G & you’ll get the YTD profit rates for all successive 10 days at once.

calculate ytd with sumifs and helper column


6. Determining YTD Portfolio Returns for Stocks & Bonds

This is the easiest part now to calculate YTD portfolio returns as it requires very simple formula.

📌 Steps:

➤ In Cell F5, we have to type:

=$E5/$E$5-1

➤ Press Enter & you’ll get the first value as 0.

➤ Now convert the whole column into percentage by selecting Percentage format from the drop-down in the Number group of commands under the Home tab.

➤ Use Fill Handle to autofill the other YTD returns for all months.

calculate yrd portfolio returns for stocks and bonds


7. Merging SUM, OFFSET & COUNTA Functions Together to Compare YTD

To compare the cumulative values between two specific & successive spans of time from two different years, this method is suitable enough. Here, the sales values for all months in 2020 are present in Column C & when you’ll input a value in Column D from the beginning you’ll get the comparative results up to the specific months between two years.

📌 Step 1:

➤ In Cell F11, type:

=SUM(OFFSET(C5:C16,,,COUNTA(D5:D16)))

➤ Press Enter & you’re done with the YTD for the year2020.

calculate ytd comparisons with sum counta functions

📌 Step 2:

➤ Assign the formula in Cell G11:

=SUM(D5:D16)

➤ Press Enter & now you’re done to & prepared to input data in Column D.

With these steps, we’re setting the function to display the outputs in both of the cells F11 & G11 so that when we’ll input data in Column D, then Cell F11 as well as G11 will simultaneously show YTD values up to the specific months too for the years of 2020 & 2021 respectively. Thus we’ll be able to compare cumulative sales values between those two data upto a particular time in both years.

calculate ytd comparisons with sum counta functions


8. Creating Pivot Table to Calculate YTD 

In our final method, we’ll apply Pivot Table to calculate YTD. We have a table for the sales values of 3 successive years.

calculate ytd with pivot table

📌 Steps:

➤ Select the whole table & choose Pivot Table option from the Insert ribbon.

➤ Put Months in the Rows Field  and the Year headers in Values Field.

➤ Put your mouse cursor on any of the sales value in year 2018 & open Value Field Settings from the options by right-clicking the mouse.

➤ From the Show Values As tab, select Running Total In.

➤ Press OK & you’ll see the cumulative sales value or running total for the year of 2018.

➤ Similarly, do this process for the years of 2019 & 2020.

calculate ytd with pivot table

Finally the output the data will be as seen in the picture below through which you can easily compare YTD upto a particular month for different 3 years.

calculate ytd with pivot table


Concluding Words

I hope all these methods mentioned above to calculate Year-to-Date(YTD) will prompt you to apply in your regular Excel works. If you got any questions or feedback please let us through comments. You can check out our other informative & useful articles related to Excel functions on this website.


You May Also Like to Explore

Excel Year to Date Sum Based on Month

How to Calculate Cumulative Percentage in Excel

Running Average: How to Calculate Using Excel’s Average(…) Function

How to Calculate Sales Growth Percentage in Excel

Mortgage calculator with extra payments and lump sum [Excel Template]

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo