Nazmus Sakib
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.
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.
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.
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.
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.
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.
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.
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.
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.
