actual and forecast sales per month

Pepenav
Pepenav
edited 12/09/19 inFormulas 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

    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

  • 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公式手册模板!
Hi @CamSME<\/a> <\/p>

I hope you're well and safe!<\/p>

Unfortunately, it's not possible now, but it's an excellent idea!<\/p>

Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p>

Here's a possible workaround or workarounds <\/strong><\/p>