# Formula for % complete.

Options
✭✭✭✭✭

Hi. I have a sheet made up with the column below. I am trying to make the percentage complete auto-fill based on which boxes are checked. I am not sure how I can efficiently do this. I can't figure out how to "add" the check boxes. I am hoping there is a way that I could have 9 options of % Complete.

Pulled=50%

Pulled+Source Termed=70%

Pulled+Source Termed+Source QA/QC=75%

Pulled +Source Termed+Source QA/QC+Device/instrument termed=95%

Pulled + Source Termed+Source QA/QC+Device/instrument termed+Device QA/QC=100%

Pulled+Source Termed+Device/instrument termed=90%

Pulled+Source Termed+Device/instrument termed+Device QA/QC=95%

Pulled+Device/instrument termed=70%

Pulled+Device/instrument termed+Device QA/QC=75%

• ✭✭✭✭✭
edited 08/13/20 Answer ✓
Options

I would use helper columns for this.

Add hidden columns with those formulas:

• Pulled Value: =IF([Pulled]@row=1,0.5,0)
• Source Termed Value: =IF([Source Termed]@row=1,0.2,0)
• Source QA/QC Value: =IF([Source QA/QC]@row=1,0.05,0)
• Device/instrument termed Value: =IF([Device/instrument termed]@row=1,0.2,0)
• Device QA/QC Value: =IF([Device QA/QC]@row=1,0.05,0)

Then in your final column: [% Complete], just add the Value columns.

=SUM([Pulled Value]@row, [Source Termed Value]@row, [Source QA/QC Value]@row, [Device/instrument termed Value]@row, [Device QA/QC Value]@row)

It'll be easier in the future if you want to change values or add new stages without reworking the whole formula. And also covers every possibilities, including the 9 you highlighted.

Hope it helped!

• ✭✭✭✭✭✭
edited 08/13/20
Options

It looks like you just need a lot of IF + per column. This formula just looks at each individual column, and if it is checked ( =1), then assign it a % complete, otherwise 0. From your example, it doesn't look like any of the check boxes change the value of % complete when paired with others, so I've assigned the column % complete above each checkbox to confirm. If they are different, just adjust the decimals in the formula below.

=IF(Pulled@row = 1, 0.5, 0) + IF([Source Termed]@row = 1, 0.2, 0) + IF([Source QA/QC]@row = 1, 0.05, 0) + IF([Device/Instrument Termed]@row = 1, 0.2, 0) + IF([Device QA/QC]@row = 1, 0.05, 0)

Jason Tarpinian - Sevan Technology

Smartsheet Aligned Partner

• ✭✭✭✭
Options

Do each column/step in this process equal a percentage complete of it's own? Meaning, "Pulled" is obviously 50%. Does this mean that "Source Termed" is 20%? If so, add a helper row to the top (row 1) that shows each step's individual %. Then in your % Complete column, do a SumIf of the different steps that will add up those percentages if the different boxes are checked within the row.

=sumif(([PULLED]2:[DEVICE QA/QC]2,1,[PULLED]\$1:[DEVICE QA/QC]\$1)

• ✭✭✭✭✭
edited 08/13/20 Answer ✓
Options

I would use helper columns for this.

Add hidden columns with those formulas:

• Pulled Value: =IF([Pulled]@row=1,0.5,0)
• Source Termed Value: =IF([Source Termed]@row=1,0.2,0)
• Source QA/QC Value: =IF([Source QA/QC]@row=1,0.05,0)
• Device/instrument termed Value: =IF([Device/instrument termed]@row=1,0.2,0)
• Device QA/QC Value: =IF([Device QA/QC]@row=1,0.05,0)

Then in your final column: [% Complete], just add the Value columns.

=SUM([Pulled Value]@row, [Source Termed Value]@row, [Source QA/QC Value]@row, [Device/instrument termed Value]@row, [Device QA/QC Value]@row)

It'll be easier in the future if you want to change values or add new stages without reworking the whole formula. And also covers every possibilities, including the 9 you highlighted.

Hope it helped!

• ✭✭✭✭✭
Options

Thank you all so much! Everyone is very helpful. I am going to try the hidden or "helper" columns and see if I can get that to work. I really appreciate the help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!