Previous month
The below formula is picking up the current month's sales SUM total:
=SUM(COLLECT({Sales Pipeline Amount}, {Sales Pipeline Sales Stage}, "4 - Closed Won", {Sales Pipeline Level}, "1", {Sales Pipeline Finish}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))) + SUM(COLLECT({Archive Sales Amount}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1", {Archive Sales Finish}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))))
I want to change the formula to pick up the previous month's SUM total which I have tried with - 1, 1) and - 1) but keep getting either #UNPARSEABLE, which I check for extra brackets, and then the error changes to #INCORRECT ARGUMENT:
=SUM(COLLECT({Sales Pipeline Amount}, {Sales Pipeline Sales Stage}, "4 - Closed Won", {Sales Pipeline Level}, "1", {Sales Pipeline Finish}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + SUM(COLLECT({Archive Sales Amount}, {Archive Sales Stage}, "4 - Closed Won", {Archive Sales Level 1}, "1", {Archive Sales Finish}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
I would also like to calculate quarterly SUM, if you can help me with that as well, please.
Answers
-
Adding a simple -1 with no bracket or comma after MONTH(TODAY()) solved the issue.
-
I hope you're well and safe!
Excellent! Glad you got it working!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!