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.

**Table of Contents**hide

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

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.

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

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

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

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.

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

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

**📌**** 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.

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

**📌**** 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.

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.

**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]**