Formula for Multi-Select Contact Column and Dropdown List

On my sheet I have one column titled “Inspectors”, this is a multi-select contact column that will have multiple options in it at times. I have a second column titled "Categories for Failure" this is a dropdown list which will also have multiple options selected at times.

I was hoping to create a formula that uniquely tracks individual failures by individual inspectors.

Currently when I have two contacts in the multi-select contact column it will not track on my data sheet, only when there is a single selection will it track.

My current formula is: =COUNTIFS({Categories for Failure}, CONTAINS("Writing on Unit", @cell), {Quality Inspector}, Inspector@row)

EX: Multi-Select Contact Column “Inspectors” has “Multiple Unique Names” Multi-Select Column “Categories for Failures” has “Multiple Unique Criteria”

Totaling the unique categories for failure by individual inspectors using a multi-select contact column and a dropdown list..

Hopefully this makes a bit of sense and thank you for any help you can provide!

Find more posts tagged with

Accepted answers

Paul Newcome

You don't need the AND function. The syntax from your original formula was correct. You just need to swap the CONTAINS for the HAS (proper syntax of course) and use that function for both criteria pieces.

HAS(@cell, "text string")

All comments

Paul Newcome

You will need the HAS function for both criteria sets as opposed to the CONTAINS function.

https://help.smartsheet.com/function/has

Matthew R

Thank you!

This was the formula I was trying with the HAS function involved, it is giving me an UNPARSEABLE error. I am thinking my structure is off a bit or maybe incorporating the AND function incorrectly.

Paul Newcome

HAS(@cell, "text string")

Matthew R

YOU WERE 100% SPOT ON, formula below works perfectly for what I am after.

=COUNTIFS({Categories for Failure}, HAS(@cell, "Missing Parts"), {Inspector}, HAS(@cell, "Tony Mannon"))

Can not thank you enough, a little tweaking last night and head scratching and we are good to go, proper syntax is difficult for me still haha!

Paul Newcome

Happy to help. 👍️

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.