# Trying to make a Rolling 12 sum formula

Options

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

• ✭✭✭✭✭✭
Options

How is the data set up in your Smartsheet?

• Options
• Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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?

• Options

I think i was overthinking it quite a bit

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

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• Options

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.

• ✭✭✭✭✭✭
Options

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. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!