Using COUNTIFS and DISTINCT and Exclude Values in the Same Column

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.

Find more posts tagged with

Accepted answers

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. 👍️

All comments

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. 👍️

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.