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
-
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.
Answers
-
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.
-
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)))
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!