Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭✭
    in Formulas and Functions

    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?

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭
      edited 03/21/25

      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.

      · Share on FacebookShare on Twitter
    • Community Champion

      @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.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions

    • Formula Help Countifs Percentages

      I need a formula to calculate sets of specific Date columns, and tally those date columns into a % of that set? For e.g. I have 2 groups. Each group has specific columns that make up the set for each …
      User: "Not so formula savvy"
      Answered ✓
      Formulas and Functions
      66
      16

    • RGY Button Edit Needed

      How do I edit this formula to turn button yellow when due date is 5 days away. =IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green"))) …
      User: "hicksiechick"
      Answered ✓
      Formulas and Functions
      24
      2

    • how to collect a date within a sheet?

      Hi, in the image below I have in my "extrusion" column an entry that populates by a formula (in this case "M3406 HEAD TRACK 15' is populating) I'm looking to populate the "Last Cycle Count Date" colum…
      User: "Brandon Morales"
      Answered ✓
      Formulas and Functions
      14
      3