Percent Completed Calculation


I am new to Smartsheets and looking to do a basic calculation of % completed of a status column.

I have multiple steps with task that all need to be completed for the overall project to be considered completed. I want to calculate % completed of each step and to count it completed if status = completed, and if status = not needed.

For this post lets say there are 7 tasks that would need to be marked as completed or not needed in order to equal 100%

If I write the following formula =COUNTIF(Status11:Status17, "Complete") + COUNTIF(Status11:Status17, "Not Needed") it will calculate the proper number of items marked as either completed or not needed.

However, if I try to divide that by the number of tasks to get the percentage (7 for this example) =COUNTIF(Status11:Status17, "Complete") + COUNTIF(Status11:Status17, "Not Needed")/7 the percentage is way off.

I have currently found a work around by hiding another column at this time but I feel like I should be able to calculate this without hiding a column.

Thank you in advance!

Best Answer

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 12/20/22 Answer ✓

    You need to add parenthesis so it will do all of the addition before dividing. Add a ( in front of CountIF and a second ) after the "Not Needed")

    =(COUNTIF(Status11:Status17, "Complete") + COUNTIF(Status11:Status17, "Not Needed"))/7


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!