Sum previous month data across year end

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!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!