Joining the Results of Boxes Checked on a Form Entry (2 Solutions)

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Edit: I had used numbers as my column names which in turn populated my first row. Another user made a good point that the number 1 in the first row will not actually duplicate my column name, but will read as "true" since it is a checkbox column.


The column names and formulas have been updated to account for this.


I think it's best explained with an example... We have 5 checkboxes that we will just call A through E for simplicity. User 1 selects A, C and E. User 2 selects D. User 3 selects A and B.


The sheet layout (column headers in bold):

User       A          B          C          D          E

               A           B          C          D          E

User 1     y                       y                       y

User 2                                          y

User 3     y           y


Here is how the basic JOIN(COLLECT would look using a ", " as the delimiter.

=JOIN(COLLECT(A$1:E$1, A@row:E@row, 1), ", ")

User 1: A, C, E

User 2: D

User 3: A, B

Nice and clean with no extra commas cluttering things up. Usually good enough. But then a coworker laid down a challenge. Replace the last comma (if any) with the word "and".


Well this pretty little mess accomplished that:

=SUBSTITUTE(JOIN(COLLECT(A$1:E$1, A@row:E@row, 1), ", "), ", ", " and ", COUNTIFS(A@row:E@row), 1) - 1)


User 1: A, C and E

User 2: D

User 3: A and B


Hope this helps anyone who happens to stumble upon this little thread...


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!