Setting Status column based on the number of "IS NOT BLANK" columns

MeeToo

Hello

I'm new to Smartsheet and need some help.

I'm tracking a project that for each participant, 12 different tasks need to happen to generate a complete data package. There's no set order for the tasks to occur, and there's no set time limit.

I want to use the 0/quarter/half/full symbols to track the status of each participant based on how many tasks have been completed, i.e. number of columns that are not blank.

I don't know if this should be an automated task, a function, conditional formatting, or something else. Any suggestions?

THank you

AravindGP

Hi @MeeToo

You will need to first calculate how many tasks are completed for each participant. You can have these in a separate metric sheet. The formula would be something like =COUNTIFS({reference of column containing participant name}, "Name of the participant", {reference of column containing the status of the task}, "value indicating the task is complete")

The above formula will give you a count of tasks each participant has completed. You can then write a formula in your sheet in the symbol column in the tune of =IF([column containing count of tasks complete]@row / 12 = 1, "Full", IF([column containing count of tasks complete]@row / 12 >= 0.75, "Three Quarter", IF([column containing count of tasks complete]@row / 12 >= 0.5, "Half", IF([column containing count of tasks complete]@row / 12 >= 0.25, "Quarter", "Empty"))))