sum the revenue by every 3 months?

Options
Ozu
Ozu ✭✭
edited 09/22/22 in Formulas and Functions

I need to sum the revenue by every 3 months and want this to be automatically updated in the dashboard. For instance, if It's august I need to see the revenue of May + June + July. When august ends need to see the sum of June + July + August and how to do this. Can anyone please help?


Best Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓
    Options

    If you change the months to numbers (January is 1, February is 2) I came up with this.

    =SUM(INDEX([Sales Net Revenue]:[Sales Net Revenue], MATCH(MONTH(TODAY()) - 1, months:months, 0)), INDEX([Sales Net Revenue]:[Sales Net Revenue], MATCH(MONTH(TODAY()) - 2, months:months, 0)), INDEX([Sales Net Revenue]:[Sales Net Revenue], MATCH(MONTH(TODAY()) - 3, months:months, 0)))


    Perhaps there's an easier way to do it but this works.


    It basically is getting Today's current month and subtracting 1 (previous month), then subtract 2 (2 months ago), then subtract 3 (3 months ago) and pulling the numbers from the revenue column and adding them.

    Hope this helps.

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 08/21/22 Answer ✓
    Options

    @Ozu If you added a new column called "Month" then you will need to replace the "Months" to "Month" in the formula or whatever the name you are using for that column.

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    edited 08/22/22 Answer ✓
    Options

    @Ozu you put MONTH())-2 you forgot to put MONTH(TODAY())-2

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓
    Options

    If you change the months to numbers (January is 1, February is 2) I came up with this.

    =SUM(INDEX([Sales Net Revenue]:[Sales Net Revenue], MATCH(MONTH(TODAY()) - 1, months:months, 0)), INDEX([Sales Net Revenue]:[Sales Net Revenue], MATCH(MONTH(TODAY()) - 2, months:months, 0)), INDEX([Sales Net Revenue]:[Sales Net Revenue], MATCH(MONTH(TODAY()) - 3, months:months, 0)))


    Perhaps there's an easier way to do it but this works.


    It basically is getting Today's current month and subtracting 1 (previous month), then subtract 2 (2 months ago), then subtract 3 (3 months ago) and pulling the numbers from the revenue column and adding them.

    Hope this helps.

  • Ozu
    Ozu ✭✭
    edited 08/21/22
    Options

    Thanks for all the help Michael, let me try. I think it will work.

  • Ozu
    Ozu ✭✭
    edited 08/21/22
    Options

    I am getting #UNPARSEABLE error, can you please check what I have entered wrong?


  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 08/21/22 Answer ✓
    Options

    @Ozu If you added a new column called "Month" then you will need to replace the "Months" to "Month" in the formula or whatever the name you are using for that column.

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    edited 08/22/22 Answer ✓
    Options

    @Ozu you put MONTH())-2 you forgot to put MONTH(TODAY())-2

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!