Md. Abdullah Al Murad
Year-to-Date in short YTD is used to start the first day of the current calendar year up to the current date. It is mostly used in analyzing a business or determining performance. In this article, I will show how to do year-to-date sums based on Months in Excel. Here, we will show 4 ways. Using these methods, you can easily find year to date sum based on month in Excel.
Download Practice Workbook
You can download the practice workbook from here.
4 Easy Ways to Sum Year to Date Based on Month in Excel
1. Excel Sum Year to Date Based on Month Input
Let’s see how to get the total summation of YTD based on month number input using SUM and INDEX functions. Before going to the main example, we need to know the basics of these two functions.
Syntax of SUM Function:
=SUM(number1,[number2],[number3],...)In this function, we can pass as per our requirements and get the total summation of the passed values. We can enter any numbers in the argument of the function parameter and the function will return the total number of the provided numbers.
Syntax of INDEX Function:
=INDEX(array,row_number,[col_number],[area_number])This function can take a maximum of four arguments and a minimum of two arguments.
array -> In the first section of its parameter, it takes the range of cells from where we will check the index value.
row_number -> Then comes the row number of reference or matching value.
[col_number] -> We can define or specify the column number from where the matched data will be retrieved.
[area_number] -> we can define or specify the area range number from where the matched data will be retrieved.
Now assume we have a dataset of monthly sales with Month name and Sales. I will show how to get the total sales by giving any month’s number as input.
Let’s follow the steps below to see how we can find the year-to-date sum.
STEPS:
- Firstly, enter the formula in cell C16.
=SUM(C4:INDEX(C4:C12,B15))
In the inner function of the formula INDEX(C4:C12, B15) will find out the range from Cell C4 which is the starting month of the year up to the given month number based on our output. After selecting the range, the SUM function sums up the sales and returns the value.
- After that, give input the Month number then press Enter.
- Finally, see the result on Cell C16.
Note: You cannot use any month’s name here. As we are taking only index values, if you use the month name in the input box, it will give Value Error. Also, in this section, we are considering the whole calculation for a specific year. So here we are avoiding year dependency.
Read More: How to Calculate YTD (Year-To-Date) Average in Excel (4 Approaches)
2. Calculate YTD Sum Using Dynamic Range
Let’s see how we can show any year-to-date summation dynamically. For this process, we need three functions which are SUM, OFFSET, and MONTH. Let’s see the function details.
Syntax of OFFSET Function:
=OFFSET(reference,rows,cols,[height],[width])This function can take four arguments in its parameter and among them two are elective.
reference -> This takes the reference of the cell or ranges of cells from where we will get the offset result.
rows -> Depending on this value, we can decide on several rows to move upward or downward from the ‘reference’ argument value. It can be a positive or negative value. Positive means downward and negative means upward.
cols -> Depending on this value we decide the column shift from the reference cell or starting cell.
[height] -> It allows us to specify the size of the revived range in the number of rows.
[width] -> It permits us to specify the size of the revived range in several columns.
Syntax of MONTH Function:
MONTH(serial_number)Using this function we can get any month number by entering any valid Excel date in its parameter. For example, if we have a date on cell A1 which is 12/10/2021. Now we are on another cell, let’s say on B1 if we use =MONTH(B24) then it will return 5.
Now let’s think we have a dataset of yearly sales with Month and Sale. I will show how to find out the year-to-date sum dynamically.
Initially let’s follow the steps below to apply the formula for the year 2015.
STEPS:
- First of all, enter the formula in Cell B5:
=SUM(OFFSET(B4,0,0,1,-MONTH(B3)):B4)
Let’s look at the inner function first which is OFFSET(B4,0,0,1,-MONTH(B3)). Here we are collecting the ranges from where we will start our count to do the sum. As our reference column is B4 that’s why it is passed as the first argument. Using the MONTH function we are dynamically changing the month’s number.
After selecting the ranges then we are summing up the total sales by using the SUM function.
- Secondly, drag and fill the formula to the right side and see the output.
- As a result, you will see the result in the picture below.
- Finally, use the same formula for the other years and see the result.
Read More: How to Calculate YTD (Year-to-Date) in Excel [8 simple ways]
3. Sum Values Based on Month in Excel
Let’s say that we have some order date and number of orders of the last 2 years in a dataset. Now I will show how to count or get the total number of orders based on the month.
STEPS:
- Firstly, enter the formula in Cell C22.
=SUMIFS(C4:C17,B4:B17,">="&B4,B4:B17,"<="&C21)
Here I have used the SUMIFS function. Let’s see the explanation of the formula below.
The syntax of the SUMIFS formula is:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
This function takes ranges and conditions sequentially. We can pass multiple ranges and conditions or criteria in its parameter as an argument. Firstly, our sum range is Cell C4 to Cell C17. That’s why I have passed C4:C17, then the criteria or condition range is B4:B17, then our condition is to sum the order from the starting date to our specific date. That’s why we have used “>=”&B4, B4:B17, “<=”&C21.
- Secondly, type any date on the input box (Cell C21) and press Enter.
- You can check the other table’s formula by entering the Date for the year 2020.
=SUMIFS(F4:F17,E4:E17,">="&F4,E4:E17,<="&F21)
This is how we can use this formula for calculating sum values based on Months for any year.
Read More: Growth Over Last Year Formula in Excel (A Step-by-Step Analysis)
4. Determine YTD Sum with Excel IF Function
In the last method, we will combine the IF, MONTH, and SUM functions to determine year to date sum. Using this combination, we can calculate the sum based on months. To explain the method, we will use a dataset that contains the Sales amount from the year 2019 to Feb-20. In our case, the formula will calculate the sum for 2019 first. Then, again start calculating the values for 2020.
So, let’s follow the steps below to see how we can use the IF function to find the YTD sum.
STEPS:
- Firstly, select Cell D5 and type the formula below:
=IF(MONTH(B5)=1,C5,SUM(C5,D4))
This formula checks if the month of Cell B5 is the first month of the year. If it is the first month which is January, then it will show the value of Cell C5. Otherwise, it will sum up the values of Cell C5 and Cell D4.
- Secondly, press Enter and drag the Fill Handle down to Cell D18.
- Finally, you will see the year-to-date sum as in the picture below.
- You can see that in Cell D17, it starts calculating the sum for the year 2020.
Conclusion
These are the ways to get a year-to-date sum in Excel. I have shown all the methods with their respective examples but there can be many other iterations. Moreover, I have added the practice workbook at the beginning. You can download it for practice. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.
I need a similar formula but for AVERAGE instead of SUM as the number of months keep growing. Can you help with that?
Hi KATIA,
Thanks for your comment. I am replying to you on behalf of Exceldemy. To find the average, you can use the AVERAGE function. For Method 1, you can follow the steps below.
STEPS:
1. Firstly, select Cell F5 and type the formula below:
=AVERAGE(C4:INDEX(C4:C12,E4))2. Press Enter to see the result.
3. Secondly, select the range B3:C12.
4. Press Ctrl + T to convert the range into a table.
5. A message box will appear.
6. Click OK to proceed.
7. As a result, you will see a table like the picture below.
8. Now, if you add Oct-15 in Cell B13, then the table and formula of Cell F5 will automatically update.
For Method 2, you can follow the above steps and use the formula below:
=AVERAGE(OFFSET(B4,0,0,1,-MONTH(B3)):B4)For Method 3, use the formula below and convert the range into a table:
=AVERAGEIFS(C4:C17, B4:B17, ">="&B4, B4:B17, "<="&F5)You can also find the formulas in the workbook below:
Workbook with AVERAGE Formulas.xlsx
I hope this will help you to solve your problems. Please let us know if you have other queries.
Thanks!