How to show a "Completion Status" column based off of certain cells containing value?

Hello Smartsheet community,

I have a database containing the amount of training specific employees have received. I would like to create a formula that is able to recognize what level someone is at and then the training needed for that level. If they have received the training it would show as "complete." If they have not received all of their training, it would show as "incomplete." Pictures below for detail:

This is the current sheet and how it looks:

I would like the "Completion Status" column to show that employee "Test 1" and "Test 3" are complete since they have received all trainings needed for the L1 level. I would like employees "Test 2" ", "Test 4" and "Test 5" to show as "incomplete" since they have not received all of their trainings.

Depending on the employee's level, the amount of training they receiving will be different. An L1 employee will receive 4 trainings to be fully complete, and L3 employee will receive 6 trainings to be fully complete.

Please let me know if you have any ideas on how to create this.




  • Hi @Ruby Wood

    We need to determine the employee level using If formula and then count "Trained" in the row as follows:

    =IF(level@row="L1",if(countif(Dish@row:[HC Prep]@row,"Trained")=4,"Complete","Incomplete"), IF(level@row="L3",if(countif(Dish@row:[HC Prep]@row,"Trained")=3,"Complete","Incomplete"),)

    However, this above formula will not validate the test name and may lead to wrong status is a wrong test is updated as trained and have to be careful while updating the test result.



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/13/21

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hello,

    I did this and now the outcome of this formula shows a 0 only for the employees and L3 and L3+ status.

    Here is the formula used:

    =IF(Level@row = "L1", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 6, "Complete", "Incomplete")) + IF(Level@row = "L1+", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 10, "Complete", "Incomplete")) + IF(Level@row = "L3", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 14, "Complete", "Incomplete")) + IF(Level@row = "L3+", IF(COUNTIF(Dish@row:[LC Lead]@row, "Trained") = 17, "Complete", "Incomplete")).

    Do you have any idea why it would be this way?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!