Get startedGet started for free

Calculating Year-to-Date

1. Calculating Year-to-Date

Let’s have a look at how we can calculate the YTD values by using a cookies business. We have 3 types of cookies chocolate, oatmeal and regular, and each cookie comes in packs of 5 and in packs of 10. The data ranges from January 2022 until June 2023. And the cookies sales per month are displayed in column E. As a short reminder, Year-to-date means we want to sum up all the sales from the beginning of the year, until the current moment for one specific product. The YTD values for March 2023 for example, is the sum of sales in January, February and March 2023. We can calculate the YTD sales using a SUMIFS formula. The first argument is the sum_range: this is the range we would like to sum - thus the sales per month. The next arguments consist of multiple criteria ranges and criteria. The first criteria range we want to select is the product column, and set it equal to the chocolate cookies. Let’s add a second criteria range for the pack size, and a third one for the year. If we press enter now and copy our formula, we notice that we are summing all sales for chocolate cookies for the year for a certain pack size. The last criteria_range we need to add is the months column. We only want to sum up the Sales per month for the months which are smaller or equal to the current month in the table. We can add this to the formula by wrapping it in quotation marks and adding an ampersand. Notice the less than or equal to sign is inside the quotation marks, but the ampersand is not. Let’s extend the formula to the rest of the table. That's it! We can do a quick check, and see that YTD is correctly calculated for the first rows of our table. After 6 months there is indeed 6600 dollar in 5 pack chocolate cookie sales for the year 2022. You can see that the YTD sales are increasing month after month. But whenever we get to a new year, you can see that the calculation starts all over again. And this is exactly the same for when we get to a new pack size, or even a new product. Having these values in your table will come in incredibly handy during your analysis, now, over to you!

2. Let's practice!

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.