#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# How to use the Effort (People) Symbols with Formula

Options
edited 12/09/19

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.

Tags:

• Employee
Options

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

• Options

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.

• Options

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.

• Employee
Options

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

• Options

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")))

• Options

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

This discussion has been closed.