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

fornor1

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

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.