Formula regarding Checkbox Column

Drea Mora

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!

Bassam Khalil

@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

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

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.

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

@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

Drea Mora

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