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

`=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 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(C5:INDEX(C5:C13,B16))`

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

`=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(C5,0,0,1,-MONTH(C4)):C5)`

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.

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

**STEPS:**

- Firstly, enter the formula in cell
**C19**.

`=SUMIFS(C6:C16, B6:B16, ">="&B6, B6:B16, "<="&C18)`

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

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))`

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

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

AVERAGEfunction. ForMethod 1, you can follow the steps below.STEPS:1. Firstly, select

Cell F5and type the formula below:`=AVERAGE(C4:INDEX(C4:C12,E4))`

2. Press

Enterto see the result.3. Secondly, select the

range B3:C12.4. Press

Ctrl + Tto convert the range into a table.5. A message box will appear.

6. Click

OKto proceed.7. As a result, you will see a table like the picture below.

8. Now, if you add

Oct-15inCell B13, then the table and formula ofCell F5will 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.xlsxI hope this will help you to solve your problems. Please let us know if you have other queries.

Thanks!