Using COUNTIFS and DISTINCT and Exclude Values in the Same Column

Jamie Dent

I want to count distinct names and need to remove child rows (within the same column) with different references text from the count. This is a report formula referencing another sheet.

=COUNT(DISTINCT({Team db & Tracker Range 2}))

That formula works great to get an over all unique count of names.

The text I do not wanted counted as part of Range 2 are: "Former" and "Master".

I've tried various forms of COUNTIFS, NOT, and <>. However, I cannot figure out how to count unique names AND exclude these the two text names of Former and Master.

Thanks in advance for any advice.

Paul Newcome

You would use a COUNT/DISTINCT/COLLECT

=COUNT(DISTINCT(COLLECT({Name Column}, {Name Column}, AND(@cell <> "Closed", @cell <> "Save"))))

Paul Newcome

Happy to help. 👍️

Paul Newcome

Are you able to provide some screenshots of sample data for context?

Jamie Dent

Hi Paul,

Thank you for the response. I created a sample.

Basically, I want to count the distinct names by excluding the cells with items listed as Closed and Save in this example.

The count in this example that I am looking for would be three = Name 1, Name 2, Name 3 as unique by excluding Closed and Save in the count.

Paul Newcome

You would use a COUNT/DISTINCT/COLLECT

=COUNT(DISTINCT(COLLECT({Name Column}, {Name Column}, AND(@cell <> "Closed", @cell <> "Save"))))

Jamie Dent

Morning Paul,

I was able to get this to work. I think the challenge at first was that I was trying to run a formula in one sheet on another sheet. Each time I add one of the <> I get a reduce number in my count by 1. Prefect.

=COUNT(DISTINCT(COLLECT({Team db & Tracker Range 2}, {Team db & Tracker Range 2}, AND(@cell <> "Closed", @cell <> "Save", @cell <> "Anticipated"))))

I greatly appreciate your help and have benefited from your previous help of others in this forum.

Jamie

Paul Newcome

Happy to help. 👍️