Sum previous month data across year end

Lynn Strahm

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!

Paul Newcome

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.

Paul Newcome

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.

Lynn Strahm

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)))

Paul Newcome

Happy to help. 👍️