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

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