# 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.

Thanks!

Ruby

• 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.

Regards,

Gulshan

• ✭✭✭✭✭✭
edited 07/13/21

PMP Certified

[email protected]

www.mobilproject.it

• 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?

Thanks.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!