How to use the Effort (People) Symbols with Formula

cme14041

Hi Smartsheet Community!

 

I have been looking for different help tips on How Tos, on using certain symbols and my search has been coming up very vague.  I can't seem to find anything on how to use the Effort Symbols (The 1-5 people) and/or little in regards the uses of the directional symbols.  I thought maybe I found something giving me a formula on how to use the Effort symbols but something was wrong with the formula.  

Can someone help me with this?  I would like to use 3 check boxes to be automatically controlled by the effort symbols to get it to display when 3 different people have finished what they are needing to do within the sheet.  Basically after each person check their assigned task the effort symbol will display automatically if 1, 2 or all 3 people have checked the appropriate boxes.  

Can this be done?  And if so PLEASE share how so.

Thank you in advance!

Kara Lumley

Hello! 

Here's what I would do (there may be a better way):

After the three checkbox columns, add a Text Number column (this can be locked and hidden). Write a COUNTIF Formula to count the number of boxes checked.

Then, have your Effort Column, and reference the Text column - 

For example:

Text Column - 

=COUNTIF([Checkbox 1]1:[Checkbox 3]1, 1)

Effort Column:

=IF([Count Column]1 = 1, "One", IF([Count Column]1 = 2, "Two", IF([Count Column]1 = 3, "Three")))

Hope this helps,

Kara

cme14041

Unfortunately, this did not work for me.  Only 1 person lights up after clicking my 3 check boxes.  Also, I tried to build the first formula with the count of 3 check boxes and something goes wrong when I tried 3 out of 3 of my check boxes.  I can do 2 check boxes but not 3 with the count column.

Please assist.

 

cme14041

Unfortunately, this did not work for me.  Only 1 person lights up after clicking my 3 check boxes.  Also, I tried to build the first formula with the count of 3 check boxes and something goes wrong when I tried 3 out of 3 of my check boxes.  I can do 2 check boxes but not 3 with the count column.

Please assist.

Kara Lumley

Hello, 

Can you please copy/paste the formula you used?

Or if you'd like, you can share your sheet to me so I can take a look - 

kara.lumley@smartsheet.com

Best,

Kara

cme14041

For counting the 3 checkboxes I tired using the following

First I tired 

=COUNTIF([Add CloudCME]1:[Closeout Folder and PARS Entry Completed]1, 1) 

Second,  

=COUNTIF([Add CloudCME]1:[Audit Performance In Practice]1: [Closeout Folder and PARS Entry Completed]1, 1)

Third,

=COUNTIF([Add CloudCME] , [Audit Performance In Practice]1, [Closeout Folder and PARS Entry Completed]1, 1)

 

After that, I tried to use the following for activating the effort column just to see if it would work.

=IF([Add CloudCME]1 = 1, "One", IF([Closeout Folder and PARS Entry Completed]1 = 2, "Two", IF([Audit Performance In Practice]1 = 3, "Three")))

cme14041

Thank you Kara!  I was able to get it figured out with a coworker.