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

## How to Calculate YTD (Year to Date) in Excel: 9 Simple Ways

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 a number 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, type the formula in cell **E5**.

`=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 formula to the whole column.

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:** How to Sum Year to Date Based on Month in Excel

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

We can also calculate the *YTD Price Per Unit* using the combination ofÂ **SUM**,Â **IF**,Â **MONTH**, and **OFFSETÂ **functions.

**ðŸ“Œ**** Steps:**

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

âž¤ After that, type the following formula in cell **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 cells **D5Â **andÂ **C5 **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.

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

We can find a similar output by using **the 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.

**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 of *January* & *February* to compare sales data between two years- *2020* & *2021*. The useful functions for this method are **SUM**,Â **OFFSET**, andÂ **MATCHÂ **functions.

**ðŸ“Œ**** Steps:**

âž¤ Type the following formula on cell** I6**.

`=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 a percentage at once.

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

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

We can find* YTD *values up to 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 the column number up to which the calculation will be executed finally through **OFFSET**,** SUM **&** ROWS** functions together.

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

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

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

âž¤ After that, press **Enter**. 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:**

âž¤ Move onto cell **G5** & 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 a 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 a 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 a 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.

**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 presented 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 year 2020.

**ðŸ“Œ**** Step 2:**

âž¤ Next, assign the formula in cell **G11**.

`=SUM(D5:D16)`

âž¤ Thereafter, press **ENTER **& now youâ€™re done.

With these steps, weâ€™re setting the function to display the outputs in both of the cells **F11 **& **G11**. When we input data in column** D**, cells **F11** as well as **G11** will simultaneously show *YTD* values up to the specific months for the years 2020 & 2021 respectively. Thus weâ€™ll be able to compare cumulative sales values between those two data up to a particular time in both years.

**9. Creating Pivot Table to Calculate YTDÂ **

In our final method, weâ€™ll apply the **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 the **Values Field**.

âž¤ After that, put your mouse cursor on any of the sales values in the 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 a 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**, and press **ENTER**.

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

âž¤ Drag the **Fill IconÂ **toÂ **AutoFillÂ **the lower cells.

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

**Download Practice Workbook**

You can download the Practice Workbook that weâ€™ve used to prepare this article.

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

**<< Go Back to YTD Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel**

I still have a question

When you want to see in a pivot table e.g YTD3 but for one line there are no entries in period 3 the pivot table shows for this period till YTD 2 how can I create a total into the zero entries to come to YTD3

Thank you for your inquiry, INGE. It is not really clear from your inquiry what you want. I believe you want to know how we can calculate the running total when a month’s entry is zero. In that situation, the Pivot table will add 0 to the previous running total, displaying the same prior running total as before. I hope this clarifies your concerns. If you have any further queries or wish to share your documents, please post them on our Exceldemy Forum (https://exceldemy.com/forum/).

Regards

Aniruddah