Formula to count total dropdown options in list

Teagan

I have columns Skills, Total Skills, & Skills complete %

I am tracking skills and I want the total skills column to auto update when a new skill is added so the total went from 30 to 31 so the completion % is accurate without me going in and manually selecting something everytime a new skill is added. I tried SUM and COUNT formulas and got nowhere they all only seem to count selected items and that works for right now but in the future when the skill count changes it could cause an issue showing someone 140% complete.

Also, if you use the select all option it won't add a new skill once thats added so i still only got 30 showing in Total Skills instead of 31.

jmyzk_cloudsmart_jp

Hi @Teagan

My solution below is a rather tricky one. It uses the COUNTM function and the method to create Multiple Dropdown Lists by joining the list with CHAR(10).

Get all the options on the list by "Select All"

The Skills column has 30 options, from skill 01 to skill 30. I selected all the options in the first row.

Create a list of all the selected and added skills in a Sheet Summary field in a text format joined by CHAR(10).

I assumed the Skills column does not check the "restrict to list values only" option, allowing users to add skills not listed in the original lists. For example, in the second row, Juni added "skill 31', while Demo, in the 3rd row, selected existing skills 01 to 04 and skill 31.

In the Sheet Summary, I added a field JOIN, which joins all the selected skills in the Skills column and combines with the delineator, CHAR(10). Since the JOIN function converts the Multiple Dropdown List values to a text of options delineated by CHAR(10), the resulting text value is in a format like this:

"skill 01" + CHAR(10) + "skill 02" + CHAR(10) + "skill 03" + CHAR(10) + "skill 04" + CHAR(10) + "skill 05" + CHAR(10) + "skill 06" + CHAR(10) + "skill 07" + CHAR(10) + "skill 08" + CHAR(10) + "skill 09" + CHAR(10) + "skill 10" + CHAR(10) + "skill 11" + CHAR(10) + "skill 12" + CHAR(10) + "skill 13" + CHAR(10) + "skill 14" + CHAR(10) + "skill 15" + CHAR(10) + "skill 16" + CHAR(10) + "skill 17" + CHAR(10) + "skill 18" + CHAR(10) + "skill 19" + CHAR(10) + "skill 20" + CHAR(10) + "skill 21" + CHAR(10) + "skill 22" + CHAR(10) + "skill 23" + CHAR(10) + "skill 24" + CHAR(10) + "skill 25" + CHAR(10) + "skill 26" + CHAR(10) + "skill 27" + CHAR(10) + "skill 28" + CHAR(10) + "skill 29" + CHAR(10) + "skill 30" + CHAR(10) + "skill 31" + CHAR(10) + "skill 01" + CHAR(10) + "skill 02" + CHAR(10) + "skill 03" + CHAR(10) + "skill 04" + CHAR(10) + "skill 31" + CHAR(10) + "skill 32" + CHAR(10) + "skill 01" + CHAR(10) + "skill 02" + CHAR(10) + "skill 32

The formula for JOIN in the Sheet Summary field is this;

=JOIN(Skills:Skills, CHAR(10))

For your information, in the above JOIN field text quote, the CHAR(10) in bold is the CHAR(10) added by the JOIN function, while the CHAR(10) not in bold is the original separator of the multiple dropdown list.

Convert text to Multiple Dropdown list.

In the [Join all Skills Selected and put in Multiple List] column in the first row, I converted the text value in the JOIN field with this formula;

=JOIN# (Smartsheet uses # refer a Sheet Summary field in a formula.)

Note that the column is set as "Allow multiple values per cell" as multiple dropdown lists.

When the column converts the JOIN# text value to multiple dropdown lists, it removes the duplicate list in the JOIN# text, such as skills 01 to 04 and 31 and 32. So, it acts like the DISTINCT function.

Get the Total Skills Number by COUNTM

Lastly, with the distinct multiple dropdown list values, we can use the COUNTM function in the Sheet Summary field to get the Total Skills Number;

[Total Skills] =COUNTM([Join all Skills Selected and put in Multiple List]$1)

Then, you will get the [Skills complete %.] with this formula;

=COUNTM(Skills@row) / [Total Skills]#

You can check the details of the formulas and how they work by accessing the published demo sheet in the link below.

https://app.smartsheet.com/b/publish?EQBCT=58a239fb0caf40bd99dc469d12cb0766

How to update the list of options

Adding skills that are not initially available would be inconvenient because the added skills would not be selectable from the list.
It would be a good idea to update the options occasionally to resolve this.
To do so, paste the value of Join all Skills Selected and put in Multiple List into a text editor.
Then, once the Skills column is set to TEXT/Number, paste the options you just pasted into the text editor into the empty rows.
Again, change the column properties back to the dropdown list. This will add the newly added list to the options.
Do the "Select All" on the first line again with this updated option. This will update the Fields in the Sheet Summary, and you can now use this method with the new, updated options.

Below is another demo sheet, updated with the list of options in the above method.

https://app.smartsheet.com/b/publish?EQBCT=2e20886bdb0c4acda11525bf688e348f

jmyzk_cloudsmart_jp

Hi @Teagan

My solution below is a rather tricky one. It uses the COUNTM function and the method to create Multiple Dropdown Lists by joining the list with CHAR(10).

Get all the options on the list by "Select All"

The Skills column has 30 options, from skill 01 to skill 30. I selected all the options in the first row.

Create a list of all the selected and added skills in a Sheet Summary field in a text format joined by CHAR(10).

I assumed the Skills column does not check the "restrict to list values only" option, allowing users to add skills not listed in the original lists. For example, in the second row, Juni added "skill 31', while Demo, in the 3rd row, selected existing skills 01 to 04 and skill 31.

In the Sheet Summary, I added a field JOIN, which joins all the selected skills in the Skills column and combines with the delineator, CHAR(10). Since the JOIN function converts the Multiple Dropdown List values to a text of options delineated by CHAR(10), the resulting text value is in a format like this:

"skill 01" + CHAR(10) + "skill 02" + CHAR(10) + "skill 03" + CHAR(10) + "skill 04" + CHAR(10) + "skill 05" + CHAR(10) + "skill 06" + CHAR(10) + "skill 07" + CHAR(10) + "skill 08" + CHAR(10) + "skill 09" + CHAR(10) + "skill 10" + CHAR(10) + "skill 11" + CHAR(10) + "skill 12" + CHAR(10) + "skill 13" + CHAR(10) + "skill 14" + CHAR(10) + "skill 15" + CHAR(10) + "skill 16" + CHAR(10) + "skill 17" + CHAR(10) + "skill 18" + CHAR(10) + "skill 19" + CHAR(10) + "skill 20" + CHAR(10) + "skill 21" + CHAR(10) + "skill 22" + CHAR(10) + "skill 23" + CHAR(10) + "skill 24" + CHAR(10) + "skill 25" + CHAR(10) + "skill 26" + CHAR(10) + "skill 27" + CHAR(10) + "skill 28" + CHAR(10) + "skill 29" + CHAR(10) + "skill 30" + CHAR(10) + "skill 31" + CHAR(10) + "skill 01" + CHAR(10) + "skill 02" + CHAR(10) + "skill 03" + CHAR(10) + "skill 04" + CHAR(10) + "skill 31" + CHAR(10) + "skill 32" + CHAR(10) + "skill 01" + CHAR(10) + "skill 02" + CHAR(10) + "skill 32

The formula for JOIN in the Sheet Summary field is this;

=JOIN(Skills:Skills, CHAR(10))

For your information, in the above JOIN field text quote, the CHAR(10) in bold is the CHAR(10) added by the JOIN function, while the CHAR(10) not in bold is the original separator of the multiple dropdown list.

Convert text to Multiple Dropdown list.

In the [Join all Skills Selected and put in Multiple List] column in the first row, I converted the text value in the JOIN field with this formula;

=JOIN# (Smartsheet uses # refer a Sheet Summary field in a formula.)

Note that the column is set as "Allow multiple values per cell" as multiple dropdown lists.

When the column converts the JOIN# text value to multiple dropdown lists, it removes the duplicate list in the JOIN# text, such as skills 01 to 04 and 31 and 32. So, it acts like the DISTINCT function.

Get the Total Skills Number by COUNTM

Lastly, with the distinct multiple dropdown list values, we can use the COUNTM function in the Sheet Summary field to get the Total Skills Number;

[Total Skills] =COUNTM([Join all Skills Selected and put in Multiple List]$1)

Then, you will get the [Skills complete %.] with this formula;

=COUNTM(Skills@row) / [Total Skills]#

You can check the details of the formulas and how they work by accessing the published demo sheet in the link below.

https://app.smartsheet.com/b/publish?EQBCT=58a239fb0caf40bd99dc469d12cb0766

How to update the list of options

Adding skills that are not initially available would be inconvenient because the added skills would not be selectable from the list.
It would be a good idea to update the options occasionally to resolve this.
To do so, paste the value of Join all Skills Selected and put in Multiple List into a text editor.
Then, once the Skills column is set to TEXT/Number, paste the options you just pasted into the text editor into the empty rows.
Again, change the column properties back to the dropdown list. This will add the newly added list to the options.
Do the "Select All" on the first line again with this updated option. This will update the Fields in the Sheet Summary, and you can now use this method with the new, updated options.

Below is another demo sheet, updated with the list of options in the above method.

https://app.smartsheet.com/b/publish?EQBCT=2e20886bdb0c4acda11525bf688e348f

Teagan

Wow @jmyzk_cloudsmart_jp Thank you so much. I will be trying to implement this in my sheet this week. You put in a ton of work for this. I can't tell you how grateful I am for this answer. I'll post again when I get it working.

jmyzk_cloudsmart_jp

Wish it works!😁

Teagan

I'm late to tell you it worked Thank you again so much!

jmyzk_cloudsmart_jp

Happy to help!😁