JOIN is missing items when joining multi-select dropdown column

Stephanie Lapera

I have a dropdown column (multi-select) that lists participating districts. Each row is a different program that can have different districts or sometimes the districts repeat. I am trying to count how many unique districts participated.

In a separate column (dropdown - multi-select), I am trying to use JOIN to pull all the districts so I can then count them.

Columns:

1. Districts: Districts per program
2. All Districts: Joining all Districts (single cell)
3. Total Districts: Total count of all districts (single cell)

Formula in All Districts column: =JOIN([Districts:Districts, CHAR(10))

Formula in Total Districts: =COUNTM([All Districts]@row)

The issue is that I know the JOIN is not pulling all districts. It is leaving a few out, so that is making my total Districts value incorrect.

If I split the JOIN up and only do a few cells at a time, it works fine, and nothing gets missed. But I can't figure out why when I try to do it all together it leaves some districts out of the join. I need it all to pull into one, so I don't count districts more than once, which happens when I split it up into smaller joins.

Any thoughts or help would be greatly appreciated. Thanks!

jmyzk_cloudsmart_jp

Hi @Stephanie Lapera

Your issue is that after using the JOIN function, your “[All Districts]” cell holds all district values as a single text string rather than a range or set of individual items. Because of this, COUNTM doesn’t interpret the cell’s contents as separate values—it just sees one continuous piece of text.

Unfortunately, Smartsheet currently has a function to get a range of constituting lists from multiple dropdown lists.

However, there are a couple of workaround approaches you can try:

Substitution method

If the District values are "A, B, C, D," first FIND A with text functions like FIND, LEFT, or MID.
Then, use the SUBSITUTE function to get "B, C, D."
Use the same text function to get B.
Repeat this process to get all the elements.

Below is an example solution using such a method.

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

I will put the formulas at the bottom.

Separate Sheet - Use the FIND function Method.

Another approach is to store the joined text in one cell and use a helper sheet (or columns) that leverages FIND and MID functions to extract each district individually. By dragging down formulas, similar to Excel, you can identify line breaks (CHAR(10)) to isolate each district name into its own cell. Once you have a column of individual districts, you can then use COUNT(DISTINCT(...)) to return the number of unique districts.

In the demo sheet below, the ALL Districts value in the Sheet Summary uses the same formula as yours, except the JOIN refers to a range in a different sheet;

=JOIN({Districts}, CHAR(10))

In the Districts column, all the values of the "All Districts" field are populated with the MID function using the CHAR(10) position in the [Find CHAR(10)] column.

The formulas from the second row are like these;

=FIND(CHAR(10), [All Districts]#)

=FIND(CHAR(10), [All Districts]#, [Find CHAR(10)]2 + 1)

=FIND(CHAR(10), [All Districts]#, [Find CHAR(10)]3 + 1)

=FIND(CHAR(10), [All Districts]#, [Find CHAR(10)]3 + 1)

This method's merit is that as the formulas are cell formulas, you can pull them down as you do in Excel to copy the same structure formula, changing the row position.

Once you get the Districts column values, you can use the COUNT(DISTINCT({range})) function to get the number.

=COUNT(DISTINCT(Districts:Districts))

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

Substitution method formulas

Note: [D0] is a helper column to make the formula shorter.

[D0] =Districts@row
[D1] =IFERROR(MID([D0]@row, 1, FIND(CHAR(10), [D0]@row)), "")
[D2] =IF(NOT(CONTAINS(CHAR(10), [D-1]@row)), [D-1]@row, MID([D-1]@row, 1, FIND(CHAR(10), [D-1]@row)))
[D3] =IF(NOT(CONTAINS(CHAR(10), [D-2]@row)), [D-2]@row, MID([D-2]@row, 1, FIND(CHAR(10), [D-2]@row)))

. . .
[D9] =IF(NOT(CONTAINS(CHAR(10), [D-8]@row)), [D-8]@row, MID([D-8]@row, 1, FIND(CHAR(10), [D-8]@row)))
[D10] =IF(NOT(CONTAINS(CHAR(10), [D-9]@row)), [D-9]@row, MID([D-9]@row, 1, FIND(CHAR(10), [D-9]@row)))
[D-1] =IFERROR(SUBSTITUTE([D0]@row, [D1]@row, ""), "")
[D-2] =IFERROR(SUBSTITUTE([D-1]@row, [D2]@row, ""), "")
[D-3] =IFERROR(SUBSTITUTE([D-2]@row, [D3]@row, ""), "")

. . .

[D-9] =IFERROR(SUBSTITUTE([D-8]@row, [D9]@row, ""), "")
[D-10] =IFERROR(SUBSTITUTE([D-9]@row, [D10]@row, ""), "")

jmyzk_cloudsmart_jp

Hi @Stephanie Lapera

Your issue is that after using the JOIN function, your “[All Districts]” cell holds all district values as a single text string rather than a range or set of individual items. Because of this, COUNTM doesn’t interpret the cell’s contents as separate values—it just sees one continuous piece of text.

Unfortunately, Smartsheet currently has a function to get a range of constituting lists from multiple dropdown lists.

However, there are a couple of workaround approaches you can try:

Substitution method

If the District values are "A, B, C, D," first FIND A with text functions like FIND, LEFT, or MID.
Then, use the SUBSITUTE function to get "B, C, D."
Use the same text function to get B.
Repeat this process to get all the elements.

Below is an example solution using such a method.

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

I will put the formulas at the bottom.

Separate Sheet - Use the FIND function Method.

Another approach is to store the joined text in one cell and use a helper sheet (or columns) that leverages FIND and MID functions to extract each district individually. By dragging down formulas, similar to Excel, you can identify line breaks (CHAR(10)) to isolate each district name into its own cell. Once you have a column of individual districts, you can then use COUNT(DISTINCT(...)) to return the number of unique districts.

In the demo sheet below, the ALL Districts value in the Sheet Summary uses the same formula as yours, except the JOIN refers to a range in a different sheet;

=JOIN({Districts}, CHAR(10))

In the Districts column, all the values of the "All Districts" field are populated with the MID function using the CHAR(10) position in the [Find CHAR(10)] column.

The formulas from the second row are like these;

=FIND(CHAR(10), [All Districts]#)

=FIND(CHAR(10), [All Districts]#, [Find CHAR(10)]2 + 1)

=FIND(CHAR(10), [All Districts]#, [Find CHAR(10)]3 + 1)

=FIND(CHAR(10), [All Districts]#, [Find CHAR(10)]3 + 1)

This method's merit is that as the formulas are cell formulas, you can pull them down as you do in Excel to copy the same structure formula, changing the row position.

Once you get the Districts column values, you can use the COUNT(DISTINCT({range})) function to get the number.

=COUNT(DISTINCT(Districts:Districts))

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

Substitution method formulas

Note: [D0] is a helper column to make the formula shorter.

[D0] =Districts@row
[D1] =IFERROR(MID([D0]@row, 1, FIND(CHAR(10), [D0]@row)), "")
[D2] =IF(NOT(CONTAINS(CHAR(10), [D-1]@row)), [D-1]@row, MID([D-1]@row, 1, FIND(CHAR(10), [D-1]@row)))
[D3] =IF(NOT(CONTAINS(CHAR(10), [D-2]@row)), [D-2]@row, MID([D-2]@row, 1, FIND(CHAR(10), [D-2]@row)))

. . .
[D9] =IF(NOT(CONTAINS(CHAR(10), [D-8]@row)), [D-8]@row, MID([D-8]@row, 1, FIND(CHAR(10), [D-8]@row)))
[D10] =IF(NOT(CONTAINS(CHAR(10), [D-9]@row)), [D-9]@row, MID([D-9]@row, 1, FIND(CHAR(10), [D-9]@row)))
[D-1] =IFERROR(SUBSTITUTE([D0]@row, [D1]@row, ""), "")
[D-2] =IFERROR(SUBSTITUTE([D-1]@row, [D2]@row, ""), "")
[D-3] =IFERROR(SUBSTITUTE([D-2]@row, [D3]@row, ""), "")

. . .

[D-9] =IFERROR(SUBSTITUTE([D-8]@row, [D9]@row, ""), "")
[D-10] =IFERROR(SUBSTITUTE([D-9]@row, [D10]@row, ""), "")

Stephanie Lapera

Thank you. I will try these different methods out and see if they work.

I have been successfully using the JOIN and COUNTM functions. This year the forumula seemed to stop working correctly when I added a few additional districts. In the past, it was correctly pulling the districts into a single cell and the counting them. Now it is still doing that but it is miss counting (only off by maybe 3 or 4).

Hopefully your suggestions will work. I will post a follow-up once I've had a moment to try them out.

Thanks again!

Humashankar

Well-explained!

jmyzk_cloudsmart_jp

@Stephanie Lapera

If you need a copy of the sheets, please get in touch with me by email in my profile page.