4 Easy Methods to Calculate the Year-to-Date Sum (Based on Month)

Method 1 – Calculating the Year-to-Date Sum Based on Monthly Input

Example Model:

excel year to date sum based on month

STEPS:

  • Enter the following formula into cell C16.
=SUM(C5:INDEX(C5:C13,B16))

Note: This formula will calculate the range from cell C5 {the starting month} to the given month.

  • Insert the month and Press
  • The result will be shown in C17.

Note: no month can be used, only index values.

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


Method 2 – Calculating the YTD Sum Using Dynamic Range

Example Model:

Calculate YTD Sum Using Dynamic Range

STEPS:

  • Enter the following formula into cell B5.
=SUM(OFFSET(C5,0,0,1,-MONTH(C4)):C5)

  • Drag and fill the formula to the right in order to see the output.

Results:

Calculate YTD Sum Using Dynamic Range

  • Use the same formula for the other years in order to see the result.

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


Method 3 – Calculating Sum Values Based on Month in Excel

Example Model:

Sum Values Based on Month in Excel

STEPS:

  • Enter the following formula into cell C19.
=SUMIFS(C6:C16, B6:B16, ">="&B6, B6:B16, "<="&C18)

  • Type any date into the input box (cell C18) and press Enter.

Note: You can check the other table’s formula by entering the date for the year 2020.


Method 4 – Calculate the YTD Sum with Excel IF Function

Example Model:

Determine YTD Sum with Excel IF Function

STEPS:

  • Enter the following formula into cell D5.
=IF(MONTH(B5)=1,C5,SUM(C5,D4))

Note: The following formula

checks whether the month of cell B5 is the first month of the year. If the first month is January, it will show the value of cell C5. Otherwise, it will sum up the values of cell C5 and cell D4.

Note: D17 shows the year-to-date sum, it starts calculating the sum for the year 2020.


Download Practice Workbook

You can download the practice workbook from here.


 

Related Article


<< Go Back to YTD Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

2 Comments
  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.
      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!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo