Formulate % Complete for Stages on Dashboard


Overview: I have a sheet with a number of equipment items for my team to collect and log. The data is split into 3 stages. (Stage 1 = Sky blue, Stage 2 = Medium blue, Stage 3 = Grey)

If the data has not yet been gathered the column with populate a 'N'. The project team will change the cell to 'Y' if field is complete, or 'N/A' if not applicable for that piece of equipment.

Stage 1 has (5) fields and makes up 60% of the completion for that row of data.

Stage 2 has (7) fields and makes up 30% of the completion for that row of data.

Stage 3 has (2) fields which makes up 10% of the completion for that row of data.

The end of each phase is a column with a check box, indicating the overall completion of that phase.

Need: I need to populate a graph/chart on a dashboard that shows the shows the project % complete based of the total # of rows and how far along each row is within phases 1,2, and 3.

(See snippet of practice sheet below which has 24 items in the project, 3 rows are 100% complete, 5 rows are 90% complete, 1 row is 60% complete, 15 rows are under <60% complete.

Thank you!

Arielle Priday, Project Administrator

Prism Professional Services Group

4340 W. Chandler Blvd | Ste 8 | Chandler, AZ 85226

tel (480) 712-3500 | website | map | email | AZ ROC 327159


  • Leibel S
    Leibel S ✭✭✭✭✭✭


    Lets start with the data, if you are looking for a percentage complete for each row, try this (you may need to fix the column names in here)

    =SUM(SUM(COUNTIFS([Field Check and Equipment Data Gathered]@row:[Cable Code Check]@row, OR(@cell = "Y", @cell = "N/A")) * (60 / 5)) + SUM(COUNTIFS([Ard Flash analysis complete]@row:[Report reviewed]@row, OR(@cell = "Y", @cell = "N/A")) * (30 / 7)) + SUM(COUNTIFS([Label Generated]@row:[Label applied/Delivered]@row, OR(@cell = "Y", @cell = "N/A")) * (10 / 2))) / 100

    Please note for this formula to work you need to keep the layout in the sheet as it is now (you cannot move around the columns)

    I am not sure of exactly what kind of chart you want to show but i hope this helps.

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭

    I could ask 1000 questions here, but I wont.

    To start with you need a % complete column with a column formula probably a nestled IF formula.

    Then I'd use a summary field to calculate the overall percentage complete of the project. Assuming all rows are an equal amount of work it would simply be =SUM([%complete]:[%complete])/COUNT([%complete]:[%complete])

    Finally I don't see how overall % complete is a chart, its just a metric right?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!