# Sum previous month data across year end

Options

I'm trying to write a formula to summarize warranty dollars for "treaters" in December to present on a dashboard. Below is what I have for a formula but it is saying "#invalid operation" and I haven't been able to figure out what I'm doing wrong.

=SUMIFS(Treater:Treater, Year:Year, IF(Month:Month = 1, =(YEAR(TODAY()) - 1), =YEAR(TODAY())), Month:Month, =(MONTH(TODAY()) - 1))

Here's what I was using that worked until we crossed into the new year: =SUMIFS(Treater:Treater, Year:Year, =YEAR(TODAY()), (Month:Month), =(MONTH(TODAY()) - 1))

Any suggestions would be greatly appreciated! Thanks!

• ✭✭✭✭✭✭
Options

The problem is with the MONTH function. Today's month number is 1. 1 - 1 = 0 which is not a valid month number.

You would want to use the same idea that you did for the year with the IF statement and say that if the current month number is 1, then output 12, otherwise do the current month number minus 1.

• ✭✭✭✭✭✭
Options

The problem is with the MONTH function. Today's month number is 1. 1 - 1 = 0 which is not a valid month number.

You would want to use the same idea that you did for the year with the IF statement and say that if the current month number is 1, then output 12, otherwise do the current month number minus 1.

• Options

Got it, Thanks Paul! I also had a couple extra "=" in there that were causing problems. Here's the formula I ended with:

=SUMIFS(Treater:Treater, Year:Year, IF(MONTH(TODAY()) = 1, (YEAR(TODAY()) - 1), YEAR(TODAY())), Month:Month, IF(MONTH(TODAY()) = 1, 12, (MONTH(TODAY()) - 1)))

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!