Previous month

Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!