Using a multi-select column in reports.

Sandra Dye

I have a sheet with a multi-select column (Plaintiff Law Firms) that I want to group by and count in a report.

I have a helper sheet (Plaintiff Firm Helper Sheet) that counts the number of times the law firm appears in the sheet:

=COUNTIFS({G6 Active Cases Range 1}, HAS(@cell, [Law Firm Names]@row))

I also have a report (Cases by Jurisdiction and Plaintiff Firm) I've designed to summarize the data by jurisdiction and plaintiff firm. I want to group by the plaintiff law firm column, and count the number of times the firm appears in each jurisdiction. How do I do this?

I know I need to use the helper sheet and formulas, but need some suggestions on how to bring my report over the finish line.

Snapshots of 1) My Sheet, 2) My Helper Sheet and 3) My Report are below. Thanks!

jmyzk_cloudsmart_jp

Hi @Sandra Dye

This is an excellent question with a real-world example! I recently worked on a very similar challenge involving a multi-select dropdown column, and here’s the solution I developed. It allows you to group and count individual law firms per jurisdiction, even though Smartsheet doesn’t natively support grouping by individual values in multi-select cells.

Objective:

Split each multi-select law firm value into individual rows to support grouping and counting per firm.

Step-by-Step Solution:

1. In the main sheet (G6_Cases_Tracker), add helper columns:

[COUNTM] =COUNTM([Plaintiff Law Firms]@row )
[#] =SUMIFS(COUNTM:COUNTM, Row:Row, <=Row@row )
[#-1] =IF(Row@row > 1, INDEX([#]:[#], Row@row - 1), 0)

This provides a running index, [COUNTM], which indicates the number of law firms associated, and [#], which is the cumulative value. With this [#] value, we can duplicate the same information from this sheet in a separate sheet, using the Row as an ID, and the [COUNTM] and [#] values to specify how many and where to copy the information.

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

For example, as the image below shows, the Row =1 information is duplicated [COUNTM] times and ends at the [#]th row.

2. Create a second sheet (e.g., G6_Cases_Tracker_Splitted) to hold the exploded list.

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

Use these formulas to pull the relevant row data and extract the law firm by position:

[Row] =JOIN(COLLECT({G6_Cases_Tracker : Row}, {G6_Cases_Tracker : #}, >=[#]@row , {G6_Cases_Tracker Range 1}, <[#]@row ))
[Case Name & No.] =IF(ISBLANK(Row@row ), "", INDEX({G6_Cases_Tracker : Case}, VALUE(Row@row )))
[Jurisdiction] =IF(ISBLANK(Row@row ), "", INDEX({G6_Cases_Tracker : Jurisdiction}, VALUE(Row@row )))
[CM] =COUNTM([Plaintiff Law Firms]@row )
[Plaintiff Law Firms] =IF(ISBLANK(Row@row ), "", INDEX({G6_Cases_Tracker : Law Firms}, VALUE(Row@row )))
[Match] =MATCH([#]@row , COLLECT([#]:[#], [Case Name & No.]:[Case Name & No.], [Case Name & No.]@row ))
[Substitute] =SUBSTITUTE([Plaintiff Law Firms]@row , CHAR(10), "^", Match@row ) + "^"
[Find] =FIND("^", Substitute@row )
[Law Firm] =IF(ISBLANK(Row@row ), "", IF(Match@row = 1, LEFT(Substitute@row , Find@row - 1), MID(Substitute@row , INDEX(Find:Find, [#]@row - 1), Find@row - INDEX(Find:Find, [#]@row - 1) - 1)))

Explanation:

• CHAR(10) is the delimiter used in Smartsheet’s multi-select dropdowns.
• SUBSTITUTE replaces one occurrence at a time (relative to the match count).
• FIND, LEFT, and MID extract the individual firm name from the substituted string.

Result:

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

Now each row in the G6_Cases_Tracker_Splitted sheet contains one law firm per case. You can now use this sheet to create a report grouped by:

• Jurisdiction
• Individual Law Firm

And count the number of occurrences cleanly.

Let me know if you'd like a template or example sheet to test it out!

Report Unhide View

Sandra Dye

Thank you! This looks like it might do the trick. Can you provide a template/example sheet so I can do some testing?

jmyzk_cloudsmart_jp

@Sandra Dye Please get in touch with me by email on my profile page so that I can share them with you.