Help with DISTINCT Formula

Tricia Banks

I need to calculate a value that shows the following:

# of distinct institutions in a column, and out of those distinct institutions, I need to filter out the Global institutions in another column labeled "Territory". Here is what I have come up with, but it is not working:

=COUNTIF(DISTINCT(Institution:Institution), Territory:Territory, NOT(@cell = "Global"))

I saw somewhere that the COUNTIF and COUNTIFS do not work with the DISTINCT value. Is that true? Help!

Paul Newcome

It is true. You are gong to need a COUNT/DISTINCT/COLLECT.

=COUNT(DISTINCT(COLLECT(Institution:Institution, Territory:Territory, @cell <> "Global")))

Paul Newcome

It is true. You are gong to need a COUNT/DISTINCT/COLLECT.

=COUNT(DISTINCT(COLLECT(Institution:Institution, Territory:Territory, @cell <> "Global")))

Tricia Banks

Yep, that did it!! Thank you!

Paul Newcome

Happy to help. 👍️