Urgent Help Needed! - Percentage Won Formula with Checkboxes

Options

We are trying to calculate our win percentage by using a formula that calculated values from two different checkbox columns. #won/#lost+#won.

We have tried several formulas but have come up empty.

The following formula worked but it did not calculate the correct percentage and we would rather calculate the percentage by checkbox columns instead of date columns.

=COUNT(([Date Lost]:[Date Lost]) + COUNT([Date Won]:[Date Won])) / COUNT([Date Won]:[Date Won])


Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    Hi Amy,


    Try this:

    =(COUNTIF(Won:Won,1))/(COUNTIF(Won:Won,1) + COUNTIF(Lost:Lost,1)


    Let me know if it works!


    Best,

    Heather

  • Adam Tyner _Sanofi_
    Options

    Hi Amy,

    I recommend using the COUNTIF formula if you want to count by the check boxes. Perhaps this will work for you:

    =COUNTIF(Won:Won, true) / (COUNTIF(Lost:Lost, true) + COUNTIF(Won:Won, true))

    Best,

    Adam

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    Hi, Amy. I think (?) I am following what you're doing here, and my best guess is that you need a COUNTIF (which will count only cells that meet a condition - e.g., box is checked) instead of COUNT (which counts all populated cells in the range).

    Try this:

    =COUNTIF(Won:Won, 1) / COUNT(Deal:Deal)

    Where "Deal" is the name of the column on your sheet that uniquely identifies each deal (maybe [Client Name/Institution])

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!