COUNTIFS / HAS / FIND formulas not working on multi-select contact column

I have used the HAS formula successfully on a multi-select dropdown field in smartsheet using the below formula:

=COUNTIFS({Add-on func}, HAS(@cell, $Label@row), {PMO}, [EMIR Refit]$1)

where both Add-on func and PMO are multi-select dropdowns fields.

However if i try and replicate this for a contact column that permits multiple contacts i receive an error

Formula i am trying to use:

=COUNTIFS({Primary Owner}, HAS(@cell, $[Integration BA]@row), {Status}, Warranty$3))

where Primary Owner (Integratino owner (primary) is multi-select contact column and Status is single select dropdown column.

For context: Reference sheet & calculation sheet shown below. The "Unparsebale" should show as 5 but is not. can you please help? thanks

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You need to remove a closing parenthesis from the end. There should only be one there.

fornor1

nice simple one - many thanks :)

fornor1

Follow up question, is it possible to perform a distinct count of the number of Account Name or Deal Name (using my above example), whilst also perform the countifs & has() fnction above?

Paul Newcome

Not with a COUNTIFS, but you can use COUNT(DISTINCT(COLLECT(………..)))

fornor1

How would that work as i have struggled to do this in the past. example

COUNT(DISTINCT(COLLECT({Account name}, {Primary Owner}, HAS(@cell, $[Integration BA]@row), {Status}, Warranty$3)))

Paul Newcome

That should be working.

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.