Countif function looking for text string in a text/number column from a different sheet

fornor1

Hi i am trying to perform a distinct count of account name, based on a text string lookup conditional on the progress/stage of the client.

i have tried using the HAS formula as this worked previously on a reference column of multi-drop down type, however it does not seem to be working on a text/number field.

I want distinct count of account name, where product name contains "Canada" (value in Label column cell), where sales pipeline stage = value per Sales Qualified cell.

=COUNT(DISTINCT(COLLECT({Sales Pipeline Account}, {Sales Pipeline Prod Name}, HAS(@cell, $Label@row), {Sales Pipeline Stage}, [1 - Sales Qualified]$1)))

any help please

Colleen Patterson

Has searches for an exact match, vs Contains which will allow the search of a partial match.

-HAS will only search for an exact match
-CONTAINS will search for a partial match.

For example, if you have "app" in any column type, and "apple" in a multi-select column, using the CONTAINS function searching for "app" will return both app and apple. To specifically search for "app", use HAS."

fornor1

Thansk Colleeen - can you share the formula construct i can use to add the CONTAINS function within a DISTINCT COUNT function as i cannot seem to get this to work either..