The sample workbook will provide a few different scenarios for when you need to calculate YTD in Excel, such as YTD price, profit, or growth rate.

**Method 1 – Calculating YTD with SUM Functions**

Consider a business company that has sold a number of products in 12 months of a year, with the total selling prices for all months recorded in the sample table. To learn the Year-to-Date selling price per unit quality of the products for each month, use **the SUM function** by following these steps.

**Steps:**

- Type the formula in cell
**E5**.

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

- Press
**Enter**to find the*YTD*for the first month. - 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 and quantity. Here the divided part is cumulative prices for the products, and 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

**Method 2 – Applying Excel Combined Functions to Calculate YTD**

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

**Steps:**

- Make some modifications in the
*Month*column to include a specific date. Say you’re taking accounts on the*28th*day of each month. So, the first date will be 1/28/2021. - 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.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

**Method 3 – Calculating YTD Using Excel SUMPRODUCT Functions**

**Steps:**

- Type the following formula in cell
**E5:**

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

- Press
**Enter**to receive the result for the first month. - Use the
**Fill Handle**to autofill the whole column.

**Method 4 – Determining YTD Growth Rate by Using a Dynamic Formula**

Let’s compare sales data between two years for fixed successive months with YTD (see example table below). Specifically, the example will determine 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`

- Press
**Enter**. - Under the
**Home**ribbon, select**Percentage**from the drop-down in the**Number**group of commands. The decimal value will be turned into a percentage. - Use the
**Fill Handle**to**AutoFill**other YTD growth rates for all products.

**Method 5 – Calculating YTD Profits by Incorporating Excel Functions**

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

**Steps:**

- Go to cell
**I7**and insert the following:

`=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 the**Fill Handle**.

For the example, the input date is May 12, 2021. The **MONTH** function extracts the month from this date, which formula then uses as the column number up to which the calculation will be executed.

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

**Method 6 – Calculating YTD by Using Excel SUMIFS Functions**

You can extract year, month, and date values with the help of **YEARFRAC **and **DATE** functions, and then use them to find the YTD rate. For the sample dataset, selling prices of the products for 10 days have been recorded along with their cost prices. To determine the YTD profit rates for successive 10 days using **the SUMIFS function**, read below.

**Steps:**

- First, type the following formula in cell
**F5**.

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

- Press
**Enter**. This results in the fraction or percentage of the days up to the particular date in column**B**based on 365 days in a year. - Use the
**Fill Handle**to fill down the whole column**F**.

The **YEARFRAC** function returns the year fraction representing the number of whole days between **start_date** and **end_date**. The **DATE** function inside inputs the first day of the year 2021 (listed in E5).

- Go to cell
**G5**and 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`

- Press
**Enter.** - Go to the
**Home**tab,**Number**group, and select the**percentage icon (%)**. - Fill down the whole column
**G**to get the*YTD*profit rates for all successive 10 days at once.

**Method 5 – Determining YTD Portfolio Returns for Stocks & Bonds**

**Steps:**

- In cell
**F5**, write down this formula:

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

- Press
**Enter**to get the first value as 0. - Convert the whole column into a percentage by selecting the
**percentage**icon in the Number section. - Drag the
**Fill Handle**to**AutoFill**the other YTD returns for all months.

**Method 8 – Merging Excel Functions Together to Compare YTD**

To compare the cumulative values between two specific and successive timespans from two different years, you’ll need to combine a few functions. Here, the sales values for all months in 2020 are presented in column **C**. When you input a value in column** D** you’ll get the comparative results up to that specific month between the two years. This uses **SUM**, **OFFSET**, and **COUNTA **functions.

**Steps:**

- Type the following formula in cell
**F11:**

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

- Press
**Enter**and you’re done with the YTD for the year 2020.

- Put the following formula in cell
**G11**.

`=SUM(D5:D16)`

- Press
**Enter**to finish.

When you 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 and 2021 respectively. This is due to the formula in F11 working through year 2020, but only up to the months filled in 2021.

**Method 9 – Creating Pivot Table to Calculate YTD **

Consider a table for the sales values of three successive years.

**Steps:**

- First, select the whole table and choose the
**Pivot Table**option from the**Insert**ribbon. - Put
**Months**in the**Rows Field**and the**Year**headers in the**Values Field**. - Put your mouse cursor on any of the sales values in the year 2018 and open
**Value Field Settings**from the options by right-clicking. - Select the
**Show Values As**tab and**Running Total In**. - Press
**OK**to get the cumulative sales value or running total for the year of 2018. - Repeat this process for the years 2019 and 2020.

- You can see the output that shows YTD for all months for the three years.

## How to Calculate YTD Average in Excel

Use **the AVERAGE function** to calculate the YTD average.

**Steps:**

- Type the following formula in cell
**D5.**

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

- Press
**Enter**. - Drag the
**Fill Icon**to**AutoFill**the lower cells.

This calculate the YTD average per month.

**Download Practice Workbook**

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

**<< Go Back to 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