Previous month

Michelle Maas

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.

Michelle Maas

Adding a simple -1 with no bracket or comma after MONTH(TODAY()) solved the issue.

Andrée Starå

Hi @Michelle Maas

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!