Trying to make a Rolling 12 sum formula

MCSmcat20

Use Case: Have a list of number by a date. Looking to calculate a "Rolling 12", which would be the current month's number, plus the previous 11 months number.

For example, if we are currently in April, the formula needs to sum months May2019 through April2020

I am replacing an Excel sheet, with the following correct rolling 12 formula: =SUMIF(A$3:A15,">="&DATE(YEAR(A15),MONTH(A15)-11,DAY(A15)),B$3:B15)

The data set looks like below. The formula listed above would sum months Feb19 through Jan 20

Thanks in advance.

Paul Newcome

Ok. Try something like this...

=SUMIFS(Recordables:Recordables, Date:Date, AND(@cell >= DATE(YEAR(Date@row) - IF(MONTH(Date@row) < 12, 1), MONTH(Date@row) + IF(MONTH(Date@row) = 12, -11, 1), 1), @cell <= IFERROR(DATE(YEAR(Date@row), MONTH(Date@row) + 1, 1), DATE(YEAR(Date@row) + 1, 1, 1)) - 1))

Paul Newcome

I just recreated your setup in a new sheet and plugged my above formula in to start with... The totals came out correctly this time.

How is the data entered into the Recordables column? It seems as if it is not registering the 1 in the 06/01/20 row as a number.

Paul Newcome

How is the data set up in your Smartsheet?

MCSmcat20

Paul Newcome

Ok. Try something like this...

=SUMIFS(Recordables:Recordables, Date:Date, AND(@cell >= DATE(YEAR(Date@row) - IF(MONTH(Date@row) < 12, 1), MONTH(Date@row) + IF(MONTH(Date@row) = 12, -11, 1), 1), @cell <= IFERROR(DATE(YEAR(Date@row), MONTH(Date@row) + 1, 1), DATE(YEAR(Date@row) + 1, 1, 1)) - 1))

MCSmcat20

Thank you very much for taking the time to respond. it works perfectly up until the highlighted cells below. I can't seem to figure out why. after this point, it totals up everything but is one short.

I think it has to do with this section @cell <= IFERROR(DATE(YEAR(Date@row), MONTH(Date@row) + 1, 1), DATE(YEAR(Date@row) + 1, 1, 1)) - 1

Paul Newcome

That portion actually generates a date. The date it generates is the last day of the month based on whatever date is in the Date column.

So if you were to put this in another date type column, for the row with 12/1/20 it would produce 12/31/20. If the date in the Date column is 2/1/20, then that portion of the formula would generate 2/29/20. It is used for comparison to determine the "End Date" for what to pull.

It's odd that it is consiently one short. Are you using this formula on the same sheet or a different one?

MCSmcat20

i'm using the formula in the same sheet. It calculated everything correctly starting from january 2019 through May of 2020 and then is one short ever month after that.

is there a simpler way to write the formula to make it calculate only 12 cells at a time?

MCSmcat20

I think i was overthinking it quite a bit

=SUM(Recordables13:Recordables24) this seems to work.

Paul Newcome

Yes. Specifying a specific range should work, and the range should update as you dragfill.

Since you have something working, I won't make it such a priority, but I am still going to try to do some testing to try to get it figured out using a SUMIFS because now that's going to bug me. Haha.

MCSmcat20

I really appreciate your help

I will keep trying as well (riffing off of your previous formula) and if i figure it out, will let you know if you can do the same. Thank you.

Paul Newcome

I just recreated your setup in a new sheet and plugged my above formula in to start with... The totals came out correctly this time.

How is the data entered into the Recordables column? It seems as if it is not registering the 1 in the 06/01/20 row as a number.

MCSmcat20

i started over, retyped all the data, and copy and pasted your formula and it worked.

not sure what was going on, but i appreciate your help very much.

Paul Newcome

I think it had to do with that 1 being read as text instead of a number for some reason. Glad it's working for you now though. Happy to help. 👍️