Charting a multi-select column using a metrics helper sheet.

Sandra Dye

I have a sheet with a multi-select column (Law Firms) and I wish to show a chart in my dashboard that displays a count of the unique firms in that column. I created a separate metrics sheet with one column (Law Firm Names) containing each unique value contained in the multi-select column. In my other column, I'm using a formula to count the instance of each value in the Law Firm Name column that appears in the original multi-select column. My formula is not quite working out.

Sheet 1: Active Cases

Multi-Select Column: Law Firms

Sheet 2: Metrics Helper

Column 1: Law Firm Names (contains unique entries from multi-select column in other sheet)

Column 2: Count of Law Firms

Formula in Count of Law Firms: =COUNTIF({Active Cases Range 1}, HAS,@cell,[Law Firm Names]))

Can someone please provide guidance on my formula?

Kelly Moore

Hey @Sandra Dye

The syntax of your HAS function is not what Smartsheet was expecting. Try the formula below.

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

Will this work for you?

Kelly

Sandra Dye

Hi Kelly,

Thanks for your assistance. Unfortunately, that formula still yields a #UNPARSEABLE error. I don't understand what's wrong.

When referencing the other sheet, I select the entire multi-select column as the range, however; I'm a bit troubled that neither the sheet name nor column name are not referenced in the formula at all (only the range reference). I've tried everything I can think of and continue to research solutions without success.

Kelly Moore

@Sandra Dye

Sorry, I didn’t notice your [Law Firm Names] reference was also incorrect

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

You can edit the name of your cross sheet reference prior to inserting in your sheet. In fact, it’s a good practice to replace the generic Range number from Smartsheet with the column name so that the reference includes sheet name column name.