How to autopopulate % Complete based off of other cells entry


I am trying to autopopulate % complete based off of other cells YES/NO entries in the sheet.

I have 5 criteria to determine if we get to 100% Completion or not:


Paperwork Received - YES (add 20%) NO (add 0%)

Samples Received - - YES (add 20%) NO (add 0%)

HPCL Received - YES (add 20%) NO (add 0%)

Hold (Yes/No) - - YES (change to 0%) NO (add nothing )

Micros - YES (add 20%) NO (add 0%)

Discrepancies - YES (add 20%) NO (add 0%)


% Complete Column

I was trying to use IF statements to combine them but it wasnt working.


  • J Tech
    J Tech ✭✭✭✭✭


    You could try:

    =SUM(IF([Paperwork Received]@row = "YES", 20, 0),IF([Samples Received]@row = "YES", 20, 0),IF([HPCL Received]@row = "YES", 20, 0),IF([Hold]@row = "YES", 0, 0),IF([Micros]@row = "YES", 20, 0),IF([Discrepancies]@row = "YES", 20, 0))


    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • tchav
    tchav ✭✭✭✭

    That worked! Thank you so much!

    Last question:

    Now I have status symbols too in another column. How would I have the symbol colors changed based off of the percentage listed?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @tchav You would use a nested IF statement.

    =IF([% Complete]@row<= 0.50, "Red", IF([% Complete]@row<= 0.75, "Yellow", "Green"))

    You can change the decimals to whatever thresholds you need.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!