actual and forecast sales per month

Options
Pepenav
Pepenav
edited 12/09/19 in Formulas and Functions

I am trying to create a dashboard to show sales reveunues actual and projected.

I have a column expected close date which I want to use for the forecast but the dates vary and although I can use month to get a numerical indicator of each month I cant work out how to create a formula which would capture all dates and present them as monthly totals in a separate columnin same or additional sheet.

I have read quite a bit in here around but can't seem to nail  how I should do it, Hope soemone can help

revenue forecast.png

Tags:

Comments

  • eric.o
    eric.o Employee
    Options

    Hello,

     

    If you would like to create a few formulae to display all dates and present them as monthly totals you can achieve this utilizing a SUMIF in combination with a MONTH function. The formula for January could look like this.

    =SUMIF([Expected close date]:[Expected close date], MONTH(@cell) = 1, [Forecast value]:[Forecast value])

    This formula states if the "Expected close date" month at the cell level equal 1 (January), include the value at that row level to be summed. 

    Altering the 1 within the formula to the progressing month values 1-12 will give you the sum totals for the other months. 

     

    Cheers, 

    Eric  

    Smartsheet Support

  • Pepenav
    Options

    Thanks for this I will work on it and let you know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!