Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    sum the revenue by every 3 months?

    ✭✭✭
    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?

    image.png


    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • ✭✭✭✭✭
      Answer ✓

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 08/21/22 Answer ✓

      @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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      edited 08/22/22 Answer ✓

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

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭
      Answer ✓

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭
      edited 08/21/22

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

      · Share on FacebookShare on Twitter
    • ✭✭✭
      edited 08/21/22

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

      image.png


      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 08/21/22 Answer ✓

      @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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      edited 08/22/22 Answer ✓

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions