actual and forecast sales per month

Pepenav

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

eric.o

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

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