Summing previous months information

Peppey

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

Garrett Henke

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

Peppey

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

Peppey

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.

Garrett Henke

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?

Peppey

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

Garrett Henke

@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!

Garrett Henke

Peppey

Sir, this worked! Thank you so much.

Garrett Henke

@Peppey Awesome news! Happy to help!