# Summing previous months information

✭✭

Hi everyone. I am trying to add up previous months information based on todays date. For example, if its March 10th, I only want to add January and February data. If its April only add January, February and March data. I have tried numerous SUMIFS formulas by using the MONTH(),-1 and TODAY() function but nothing works. Can you help me please

## Answers

• ✭✭✭✭✭

This is a formula that I have used to sum same time last year values to the specific day.

=SUMIFS({Sum Range}, {Date Range}, AND(YEAR(@cell) = [Last Year]#, YEARDAY(@cell) < YEARDAY([Todays Date}#)))

• {Sum Range} = The values you are trying to add together
• {Date Range} = Date range that you are using (this range should be a date field, ie 2/17/2022)
• [Last Year]# = This is a sheet summary field set to =YEAR(TODAY())-1
• [Todays Date]# = This is a sheet summary field set to =TODAY()

You can tweak the formula to fit what you are trying to do by changing YEARDAY to MONTH as well as add other criteria to fit your needs

• ✭✭

Great thanks sir I will give it a try and let you know

• ✭✭

Sir unfortunately I could not get your formula to work. I have pasted a snippet below on what I am trying to add up based on the previous months from todays date.

• ✭✭✭✭✭

Yup my formula will not work because it is for taking a list of values with specific dates and then adding the values. Will your sheet always be formatted this way? Months in order chronologically and every row is a new month?

• ✭✭

Yes sir that's correct. We will always keep it in this format

• ✭✭✭✭✭
edited 02/17/22

@Peppey Try doing this:

Add a column called Month# and set it to 1 for January, 2 for February, etc.

The formula that you will want to use is this:

=IF(Month(TODAY()) = 1, ([January Value]), IF(MONTH(TODAY()) = 2, ([January Value] + [February Value]), IF(....

Keep taking the IF(MONTH(TODAY()) = and do this for January through December. It's a tedious formula but it works. Also as you build the formula copy and paste the month values from the previous if statement that way you are only adding a single month at a time and not repeating the formula over and over.

This is one that I created for a sheet:

=IF([Current Month Number]# = 1, [Jan-LY]@row, IF([Current Month Number]# = 2, ([Jan-LY]@row + [Feb-LY]@row), IF([Current Month Number]# = 3, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row), IF([Current Month Number]# = 4, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row), IF([Current Month Number]# = 5, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row), IF([Current Month Number]# = 6, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row + [Jun-LY]@row), IF([Current Month Number]# = 7, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row + [Jun-LY]@row + [Jul-LY]@row), IF([Current Month Number]# = 8, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row + [Jun-LY]@row + [Jul-LY]@row + [Aug-LY]@row), IF([Current Month Number]# = 9, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row + [Jun-LY]@row + [Jul-LY]@row + [Aug-LY]@row + [Sep-LY]@row), IF([Current Month Number]# = 10, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row + [Jun-LY]@row + [Jul-LY]@row + [Aug-LY]@row + [Sep-LY]@row + [Oct-LY]@row), IF([Current Month Number]# = 11, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row + [Jun-LY]@row + [Jul-LY]@row + [Aug-LY]@row + [Sep-LY]@row + [Oct-LY]@row + [Nov-LY]@row), IF([Current Month Number]# = 12, ([Jan-LY]@row + [Feb-LY]@row + [Mar-LY]@row + [Apr-LY]@row + [May-LY]@row + [Jun-LY]@row + [Jul-LY]@row + [Aug-LY]@row + [Sep-LY]@row + [Oct-LY]@row + [Nov-LY]@row + [Dec-LY]@row)))))))))))))

There is probably a simpler way to do this but when I originally made it this was all the brain function that I could muster so it stuck!

• ✭✭✭✭✭
• ✭✭

Sir, this worked! Thank you so much.

• ✭✭✭✭✭

@Peppey Awesome news! Happy to help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!