How to Sum Year to Date Based on Month in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 you 4 ways of how to do year-to-date sum based on the month in Excel.

How to Sum Year to Date Based on Month in Excel: 4 Easy Ways

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:


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 of the provided numbers.

Syntax of INDEX Function:


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.

excel year to date sum based on month

Let’s follow the steps below to see how we can find the year-to-date sum.


  • Firstly, enter the formula in cell C16.

INDEX(C5:C13,B16) will find out the range from cell C5 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, input the month number then press Enter.
  • Finally, see the result on cell C17.

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 a 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

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:


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:


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.

Calculate YTD Sum Using Dynamic Range

Initially let’s follow the steps below to apply the formula for the year 2015.


  • First of all, enter the formula in cell B5.

Let’s look at the inner function first which is OFFSET(C5,0,0,1,-MONTH(C4)). Here we are collecting the ranges from where we will start our count to do the sum. As our reference column is C4 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, we are summing up the total sales by using the SUM function.

  • Next, 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.

Calculate YTD Sum Using Dynamic Range

  • Finally, use the same formula for the other years and see the result.

Read More: How to Calculate YTD (Year-to-Date) in Excel

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.

Sum Values Based on Month in Excel


  • Firstly, enter the formula in cell C19.
=SUMIFS(C6:C16, B6:B16, ">="&B6, B6:B16, "<="&C18)

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 C6 to C16. Then the criteria or condition range is B6:B16. Next, our condition is to sum the order from the starting date to our specific date. That’s why we have used “>=”&B6, B6:B16, “<=”&C18.

  • Next, type any date on the input box (cell C18) and press Enter.

  • You can check the other table’s formula by entering the date for the year 2020.
=SUMIFS(F6:F16, E6:E16, ">="&F6, E6:E16, "<="&F18)

This is how we can use this formula for calculating sum values based on Months for any year.

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.

Determine YTD Sum with Excel IF Function

So, let’s follow the steps below to see how we can use the IF function to find the YTD sum.


  • Firstly, select cell D5 and type the formula below.

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.
  • Then, 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.

Download Practice Workbook

You can download the practice workbook from here.


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.

Related Article

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

  1. 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.
      1. Firstly, select Cell F5 and type the formula below:

      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:
      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF