Formula regarding Checkbox Column

Hello Smartsheet community,

I'm attempting to write a formula for a project plan type grid sheet. One of the columns is a checkbox to represent when the item is complete. I'd like to write a formula that counts all the checked activities to get management a % complete value based on how many boxes are checked and the formula auto populates when employees check more activities off. Can someone help me write this formula and tell me if i should write it directly into the sheet or write it in sheet summary?


Thank you!

Tags:

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Drea Mora 

    As i understand from your question you need the percentage of Checked cells of total cells in Done column and you need this formula to update the percentage even when new rows are added:

    please try the following formula:

    =(COUNTIFS(Done:Done, @cell = 1) / COUNT(Done:Done)) * 100
    


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Matt Foss
    Matt Foss ✭✭✭✭

    Hi Drea,

    Will the order of items completed always occur in the same order, or can a user select any step in the process to indicate as complete at any time?

    For the first option, there's a relatively straightforward nested IF approach you could use. For the second, I believe you'd need a decent number of helper columns (that could be stored on another sheet).

    Let me know which method and happy to share how I'd approach it in more detail!

    Best regards,

    Matt

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Drea Mora 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Drea Mora
    Drea Mora ✭✭✭

    Thank you so much for these comments! I'm attaching a screenshot of the sheet to this note. The action steps do not need to occur in a specific order but I'd like the functionality to allow for more steps (a new row) to be added without needing to update the formula. I'd like to know the percent of the Done column that is checked. Is that possible?


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Drea Mora 

    As i understand from your question you need the percentage of Checked cells of total cells in Done column and you need this formula to update the percentage even when new rows are added:

    please try the following formula:

    =(COUNTIFS(Done:Done, @cell = 1) / COUNT(Done:Done)) * 100
    


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Drea Mora
    Drea Mora ✭✭✭

    That did it!!!! Thank you so much @Bassam Khalil

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!