Microsoft Excel has provided a wide range of solutions to calculate **YTD** (Year-to-Date) in **Excel** 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 Practice Workbook**

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

**9 Suitable Approaches to Calculate YTD in Excel**

In the workbook, we’ll try to show you some different applications of **YTD **using some Excel functions. **YTD** price, profit, growth rate etc. are discussed in this article. Please follow the next sections to get the best idea of how to calculate **YTD **in Excel.

**1. Calculating YTD with SUM Functions**

First, let’s get introduced to our dataset. 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. We can do this simply by using **the SUM function**.

**📌**** Steps:**

➤ First, in** Cell E5**, type:

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

➤ After that, press **Enter** & we’ll find the **YTD** for the first month.

➤ Next, use the **Fill Handle** to **AutoFill** 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.

**Read More:** **Excel Year to Date Sum Based on Month (3 Easy Ways)**

### 2. Applying Excel Combined Functions to Calculate YTD

We can also calculate **YTD Price Per Unit **using the combination of **SUM**, **IF**, **MONTH **and **OFFSET **functions. Let’s have a look at the description below.

**📌**** Steps:**

➤ First, make some modifications in the **Month** column. Say we are taking accounts in the **28th **day of each month. So the first date will be **1/28/2021**.

➤ After that, type the following formula in **E5 **and press the **ENTER **button. You will see the **YTD ****Price Per Unit **in **January**.

`=IF(MONTH(B5)=1,D5/C5,SUM(OFFSET($D$5,0,0,1,1):D5)/SUM(OFFSET($C$5,0,0,1,1):C5))`

The formula references the **D5 **and **C5 **cells using **the OFFSET function**. It removes the **‘Formula Omits Adjacent Cells’ **error. The **IF function **returns the **Price per Unit **of **January **if the month in **B5 **is the first month of the year. Otherwise it returns values for the later months of the year.

➤ Next, use the **Fill Handle **to **AutoFill **the lower cells.

Thus you can calculate **YTD **using **SUM**, **IF**, **MONTH **and **OFFSET **functions.

**3. Calculate YTD Using Excel SUMPRODUCT Functions**

We can find the similar output by using **SUMPRODUCT** function too.

**📌**** Steps:**

➤ First, type the following formula in **Cell E5**.

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

➤ After that, press **Enter** & you’ll get the result for the first month.

➤ Next, use **Fill Handle** again to autofill the whole column.

**Read More:** **Growth Over Last Year Formula in Excel (A Step-by-Step Analysis)**

**4. Determining YTD Growth Rate by Using Dynamic Formula**

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. The useful functions for this method are **SUM**, **OFFSET **and **MATCH **functions.

**📌**** Steps:**

➤ First, 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`

➤ Next, press **Enter**.

➤ After that, 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.

➤ Thereafter, use **Fill Handle** to **AutoFill** other** YTD** growth rates for all products.

**Read More:** **How to Calculate YTD (Year-To-Date) Average in Excel (4 Approaches)**

**5. Calculating YTD Profits by Incorporating Excel Functions**

We can find** YTD** values upto a particular month by inserting a specific date by using **SUM**, **OFFSET**, **ROWS **and **MONTH **functions.

**📌**** Steps:**

➤ First, in **Cell I7**, our formula for this criterion will be:

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

➤ After that, press **Enter**.

➤ Thereafter, 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.

**Read More:** **How to Calculate MTD (Month to Date) in Excel (3 Methods)**

**6. Calculating YTD by Using Excel SUMIFS Functions**

In this section, we’ll extract year, month & date with the help of **YEARFRAC & 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 using **the SUMIFS function**.

**📌**** Step 1:**

➤ First, type the following formula in **F5**.

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

➤ After that, 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.

➤ Next, 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:**

➤ First, move onto **Cell G5** now & type the formula below.

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

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

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

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

➤ First, in **Cell F5**, write down the formula below.

`=$E5/$E$5-1`

➤ Next, press **Enter **& you’ll get the first value as **0**.

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

➤ Later, use **Fill Handle **to **AutoFill** the other YTD returns for all months.

Thus, you can determine the **YTD Portfolio Returns**.

**Read More:** **Growth Formula in Excel with Negative Numbers (3 Examples)**

**8. Merging Excel 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. We will be using **SUM**, **OFFSET **and **COUNTA **functions in this regard.

**📌**** Step 1:**

➤ First, type the following formula In **Cell F11**.

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

➤ After that, press** Enter** & you’re done with the **YTD** for the year2020.

**📌**** Step 2:**

➤ Next, assign the formula in **Cell G11**:

`=SUM(D5:D16)`

➤ Thereafter, 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.

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

➤ First, select the whole table & choose **Pivot Table** option from the Insert ribbon.

➤ Next, put **Months** in the **Rows Field ** and the **Year **headers in **Values Field**.

➤ After that, 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.

➤ Thereafter, select **Show Values As tab **>> **Running Total In**.

➤ Later, 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, you can see the output through which you can easily compare **YTD** to a particular month for different 3 years.

## How to Calculate YTD Average in Excel

Excel has function to calculate the **YTD **average too. We will use **the AVERAGE function** to calculate the **YTD **average. Let’s have a look at the following description. We will use the monthly total price only for this purpose.

**📌**** Steps:**

➤ Type the following formula in cell **D5**, press **ENTER **and drag the **Fill Icon **to **AutoFill **the lower cells.

`=AVERAGE($C$5:C5)`

Thus you can calculate **YTD **average by using the **AVERAGE **function.

**Concluding Words**

In the end, I hope all these methods mentioned above to calculate **YTD **or** Year to Date** in Excel 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.