Computer Tutoring Logo

Use SUM Function Up To the Previous Month

SUM By Month - Using AutoSum to SUM to the Previous Month

What is Sum by Month? The following scenario was posed by one of our clients. She wanted to use AutoSum to sum up the sales of the months of the year. However she only wanted to include the current month after the current month had passed. For example if the month was September, then we would sum up to the previous month, which would be August. Then once September had passed the calculation would also include September and so on as the year progresses.

  1. Click in cell N2.
  2. Then, type the following formula:
    =SUM(INDEX(B2:M2,1): INDEX(B2:M2, MONTH(TODAY())-1)

    As a word of explanation, the Month() function extracts the month from the Today() function. The Today() function gets today's date. We then minus one to the figure so that we return the month number from the previous month.
  3. AutoFill the rest of the figures down.

More details on using the Autosum feature can be found on the Microsoft Website.

As you can see the Sum by month function is, in fact, Sum with the INDEX function sandwiched between it's brackets. Using this function you can sum values to the previous month.

Build your Excel skills further: Want to develop these skills with an instructor? View our Microsoft Excel training courses for live online, onsite and tailored Excel training.