Hello everyone, Novice formula help needed.

I have a column named % Probability 7-day close that shows the percentage probability of closing the amount listed in the Forecast Amount column. I'm in need of a formula that can capture every time 25% is listed and sum the totals from the Forecast Amount column, so I have an overall total of every cell tagged as having a 25% chance of closing in 7-days. I'd like to do this for each percentage. 25%, 50%, 75% & 100%. Thank you in advance for taking the time out of your day to provide help. Much appreciated.


Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @C. Phillips ,

    Where are you planning on tracking this information? Will it be a summary field on this sheet or tracked in a separate sheet?

    If stored on this sheet, the formula for 25% would like the following.

    =SUMIF([% Probability 7-day close]:[% Probability 7-day close], =0.25, [Forecast Amount]:[Forecast Amount])

    For the other values, change the .25 to .5, .75, and 1.

    If you are going to track this information on a separate sheet, the ranges need to change to be cross-sheet references.

    Hope this helps,

    Dave

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @C. Phillips ,

    Where are you planning on tracking this information? Will it be a summary field on this sheet or tracked in a separate sheet?

    If stored on this sheet, the formula for 25% would like the following.

    =SUMIF([% Probability 7-day close]:[% Probability 7-day close], =0.25, [Forecast Amount]:[Forecast Amount])

    For the other values, change the .25 to .5, .75, and 1.

    If you are going to track this information on a separate sheet, the ranges need to change to be cross-sheet references.

    Hope this helps,

    Dave

  • DKazatsky2,

    This worked perfectly for my needs. Thank you very kindly for taking time out of your day to provide help and support. I truly appreciate it. Hope you have a great day!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!