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

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?

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.