**YTD** calculation is very common in evaluating business trends or comparing performance reports to rivals or peers in the same industry. This article will show how to calculate **YTD** with **8** **convenient approaches** in Excel.

## 📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.

## What Is YTD?

The term **YTD** (year to date) refers to the time between the **start of a year** and the **present date**. As a result, the **YTD** **amount of sales** represents the **total** of your **sales** from the beginning of the current year to the most recent sales.

## Learn to Calculate YTD in Excel with These 8 Easy Approaches

Here, we will learn to calculate **YTD** in Excel using some sample datasets with the help of different functions.

**Approach**

### 1. Using SUM Function

In this approach, we will calculate **YTD Delivery Charge Per Order**. It means we will determine the total Delivery Charge Per Order up to **June**.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, select cell
**E6**and**Enter**the following formula:

`=SUM($D$6:D6)/SUM($C$6:C6)`

- Then, apply the
**Fill Handle**icon from the**bottom right corner**of cell**E6**and**double-click**to apply the formula to the**E**column.

- We successfully calculated
**YTD Delivery Charge Per Order**.

** **

**Note:** If we apply the above formula, we will get a** warning **sign telling **Formula Omits Adjacent Cells**. In the **third **approach, we will learn how to avoid this warning sign.

**🔨 Formula Breakdown**

**SUM($D$6:D6)/SUM($C$6:C6)**

👉 In cell **E6**,** the SUM function** takes the** Delivery Charge** and the other **SUM** function takes the number of **Orders**. Later, we divide them.

👉 Then, in cell **E7**, the **SUM **function adds the **Delivery Charge** for **January** and **February **and the other combines the number of **Orders **for the above-mentioned months. Next, we divide them to calculate** YTD Delivery Charge Per Order** till **February**.

**Approach**

### 2. Utilizing SUMPRODUCT Function

Next, let’s see how to estimate YTD Delivery Charge Per Order using** the SUMPRODUCT function**.

⬇️⬇️ **STEPS **⬇️⬇️

- Just like the previous approach, select cell
**E6**and type in the following formula:

`=SUMPRODUCT($D$6:D6)/SUMPRODUCT($C$6:C6)`

- Afterward, use the
**Fill Handle**icon. - Voila! We now have the
**YTD Delivery Charge Per Order**till**June**.

**Note:** Just like in the **first **approach, we will get a **warning **sign telling **Formula Omits Adjacent Cells**. In the **third **approach, we will learn how to avoid this.

**🔨 Formula Breakdown**

**SUMPRODUCT($D$6:D6)/SUMPRODUCT($C$6:C6)**

👉 In cell **E6**, the **SUMPRODUCT **function takes the **Delivery Charge** and the other takes the number of **Orders**. Later, we divide them.

👉 In cell **E11**, the **SUMPRODUCT **function sums all the **Delivery Charge** till **June **and the second **SUMPRODUCT **function adds the number of **Orders **till **June **and then we divide them to calculate **YTD Delivery Charge Per Order** till **June**.

**Approach**

### 3. Merging IF, SUM, and OFFSET Functions

In this approach, we will use the **IF**, **SUM**, and **OFFSET **functions to determine the **YTD Delivery Charge Per Order**. This method will return the same outcome as the previous two methods, but without the warning icon. Let’s start!

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**E6**and**Enter**the following formula:

`=IF(B6="January",D6/C6,SUM(OFFSET($D$6,0,0,1,1):D6)/SUM(OFFSET($C$6,0,0,1,1):C6))`

- Then, use the
**Fill Handle**icon to find**YTD Delivery Charge Per Order**in column**E**. - Now, see there is no
**warning**icon beside the cells.

**🔨 Formula Breakdown**

**IF(B6=”January”,D6/C6,SUM(OFFSET($D$6,0,0,1,1):D6)/SUM(OFFSET($C$6,0,0,1,1):C6))**

👉 In cell **E6**, the **IF **function applies the condition that if cell **B6 **contains **January**, it will divide **D6 **with **C6 **to calculate the **YTD Delivery Charge per Order**. Otherwise, it will use the **OFFSET **and **SUM **functions.

👉 For instance, in cell **E8**, **IF **function will not find **January **in cell **B8**. Then it will take the values of cell **D6 **to cell **D8 **using the first **OFFSET **function and add them utilizing the **SUM **function. Similarly, the values of cells **C6 **to **C8 **will be totaled. Later, the added values will be divided to determine the **YTD Delivery Charge per Order** till **March**.

**Approach**

### 4. Combination of SUM and OFFSET Functions

Here, we will work with a different dataset to determine YTD till March. It means we will total all the sales of every product till March.

⬇️⬇️ **STEPS **⬇️⬇️

- First, select cell
**G6**and write the formula below, and press**Enter**:

`=SUM(OFFSET($C$5:$E$5,1,0,ROWS($B$6:B6),3))`

- At this time, use the
**Fill Handle**icon. - We have the
**total sales**of all the products up to**March**.

**🔨 Formula Breakdown**

**SUM(OFFSET($C$5:$E$5,1,0,ROWS($B$6:B6),3))**

👉 Let’s see what** the ROWS function **returns. Here the **ROWS **function returns the number of rows from cells **B6 **to **B6 **which is **1**.

👉 Now the **OFFSET **function returns the reference of cells just one row below cells **C5 **to **E5 **which are cells **C6 **to **E6**. Later, the **SUM **function adds the value of **1 row **by **3 columns** which is the value of cells **C6 **to **E6**.

👉 In cell **G7**, the formula adds the value of cells **C6 **to **E7**.

**Approach**

### 5. Combining SUM, MATCH, and OFFSET Functions

Here, we will learn how to calculate** YTD Growth Rate**. We will combine the sales of **January**, **February**, and **March**, **April**. Then we will compare if the sales of a particular product increased or decreased.

⬇️⬇️ **STEPS **⬇️⬇️

- First of all, select cell
**G6**to**Enter**the following formula:

`=SUM(OFFSET(D5,1,1,1,MATCH("April",$E$5:$F$5,0)))/SUM(OFFSET(B5,1,1,1,MATCH("February",$C$5:$D$5,0)))-1`

- Later, use the
**Fill Handle**icon to apply the formula to the other cells. - Now, we have the
**YTD Growth Rate**for every product.

**🔨 Formula Breakdown**

**SUM(OFFSET(D5,1,1,1,MATCH(“April”,$E$5:$F$5,0)))/SUM(OFFSET(B5,1,1,1,MATCH(“February”,$C$5:$D$5,0)))-1**

👉 **The MATCH function** returns the number of columns in which it finds **April **from cells **E5 **to **F5 **and the other finds the number of columns where **February **is present from cells **C5 **to **D5**.

👉 Now the first **OFFSET **function returns the reference of cells **E6 **to **F6 **and the second one returns cells **C6 **to **D6**. The **SUM **function adds the values. Next, we divide them and subtract **1 **to calculate the **YTD Growth rate **for Product **D03**.

**Approach**

### 6. Applying YEAFRAC and SUMIFS Functions to Find YTD Profit

In our next method, we will show how to calculate **YTD Profit **in Excel using certain **criteria**.

⬇️⬇️ **STEPS **⬇️⬇️

- To calculate the
**YTD**gain, we need to determine the**Year Fraction**using**the YEARFRAC function**.**Enter**the formula below in cell**F6**.

`=YEARFRAC(DATE(YEAR(B6),1,1),B6,1)`

- Just like the former methods use the
**Fill Handle**icon. - Select cells
**F6**to**F11**and from the**Number Format**drop-down menu under the**Home**tab and**Number**group, click**Percentage**.

- We now have the
**Fraction**in**percentage**format which will be used as a criterion to determine the**YTD Profit**. - Later, in cell
**G6**,**Enter**the following formula:

`=SUMIFS(E:E,C:C,"Regular",F:F,"<="&F6)/SUMIFS(D:D,C:C,"Regular",F:F,"<="&F6)-1`

- Now, as always use the
**Fill Handle**icon. - Then, select cells
**G6**to**G11**to apply the**Percentage**format.

- Notice that cell
**C9**contains**Rapid**and so the**YTD Profit**remains the same because it does not match the conditions given in the**SUMIFS**function. - You have calculated the
**Profit**successfully using different conditions.

**🔨 Formula Breakdown**

**YEARFRAC(DATE(YEAR(B6),1,1),B6,1)**

👉 The **YEARFRAC **function returns a fraction of the number of days between a **start **and an **end **date.

👉 Here, we used **the YEAR function** to get the year from cell **B6**.

👉 **The DATE function **takes the year, month, and date as input. In this scenario, it will return **January 1, 2022**, as the **start date** for the **YEARFRAC** function. The Date stored in cell **B6 **is the **end **date.

**🔨 Formula Breakdown**

**SUMIFS(E:E,C:C,”Regular”,F:F,”<=”&F6)/SUMIFS(D:D,C:C,”Regular”,F:F,”<=”&F6)-1**

👉 The first **SUMIFS function** will add the values of column **E **where the function finds **Regular **in column **C **and **Year Fraction **greater than or equal to **F6 **in column **F**.

👉 The other **SUMIFS **function adds the value of column **D **where the above conditions match.

👉 Then we divide and subtract **1 **to determine** YTD Profit**.

**Approach**

### 7. Comparing YTD by Coalescing SUM, OFFSET, and COUNTA Functions

Suppose, at the end of **April 2022**, we want to compare the sales with **2020 **and **2021 **till **April**. We will merge the **SUM**, **OFFSET **with **the COUNTA function** to do that. Let’s start!

⬇️⬇️ **STEPS **⬇️⬇️

- Now, select cell
**G6**and**Enter**the given formula to total the sales of**2020**till**April**:

`=SUM(OFFSET(C6:C11,,,COUNTA($E$6:$E$11)))`

- After that, use the
**Fill Handle**icon sideways from cells**G6**to**I6**to get the**total sales**till**April**for the last three years.

**🔨 Formula Breakdown**

**SUM(OFFSET(C6:C11,,,COUNTA($E$6:$E$11)))**

👉 The** COUNTA **function counts the number of **non-empty cells** from cell **E6 **to cell **E11**.

👉 The **SUM **function then adds the values of cells **C6 **to **C9 **using the reference returned by the **OFFSET **function between cells **C6 **to **C11**.

**Approach**

### 8. Executing Pivot Table

In our last approach, we will learn how to utilize the **Pivot Table** in order to find total sales for the years **2020**, **2021**, and **2022 **till **June**.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cells
**B5**to**E11**. - Navigate to the
**Insert**tab and click**PivotTable**.

- A
**PivotTable from table or range**dialog box will appear on the screen. - Click the
**New Worksheet**button and click**OK**.

- In the new worksheet, drag the
**Month**to the**Rows**and the**Years**to the**Values**.

- Then, we will get a
**Pivot Table**like the following screenshot.

**Double-click**the**Sum of 2020**header to open the**Value Field Settings**.- Now, select
**Show Value As**. From the**drop-down**menu select**Running Total IN**and click**OK**.

- Then, do that for the rest of the years and finally, we will get the
**total sales**for each year up to**June**.

## How to Calculate YTD Average in Excel

We will also show here how to determine **YTD Average Sales** in Excel by applying** the Average function**.

⬇️⬇️ **STEPS **⬇️⬇️

- First of all, select cell
**D6**to**Enter**the formula below:

`=AVERAGE($C$6:C6)`

** **

- Then, use the
**Fill Handle**icon to apply the formula to the**Average**column. - We have found the
**YTD Average**till**June**.

## 📝 Takeaways from This Article

📌 Firstly, we used the **SUM **function to find YTD Delivery Charge Per Order.

📌 Secondly, we learned to utilize the **SUMPRODUCT **function for the same outcome.

📌 Thirdly, we merged the **IF**, **SUM**, and **OFFSET **functions to overcome the warning icon found in the previous two methods.

📌 After, we combined the **SUM**, and **OFFSET **functions to total all the sales up to a certain month.

📌 Then, we used the combination of the **SUM**, **OFFSET**, and **MATCH** functions to find YTD Growth Rate.

📌 Next, we used the **YEARFRAC **and **SUMIFS **functions to find the YTD Profit satisfying different criteria.

📌 Later, we compared the sales over three years up to April using the **SUM**, **OFFSET**, and **COUNTA **functions.

📌 In our last method, we inserted the **Pivot Table **to determine the total sales for the last three years till June.

📌 We also learned how to estimate the YTD Average by applying the **AVERAGE **function.

## Conclusion

That concludes the discussion for today. These are some convenient methods to calculate YTD in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website **ExcelDen** to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.