Counting Distinct items.

=IF(COUNTIF({Client Name (Pruning)}, [Client Name]@row) = 0, "", COUNT(COLLECT({Annabelle Name}, {Client Name (Pruning)}, @cell = [Client Name]@row)))

The above formula works great after receiving help from a member of this community :)

The formula is counting the rows with the criteria; however, there are duplicates counted for various reasons. I'd like to limit the counts of for example "Annabelle" above to one for the same submitted date on the reference sheet. In other words, if there are three rows with the date of 5/09/2025 and "Annabelle" is part of the row, I want to count it only once. Additionally, if there is another distinct date of 5/12/2025, I want that to be counted once. Consequently the four rows of info would add up to TWO.

Find more posts tagged with

functionality

Accepted answers

All comments

Melissa Yamada

Hello @Craig Lemberger

Try this:

=IF(COUNTIF({Client Name (Pruning)}, [Client Name]@row) = 0, "", COUNT(DISTINCT(COLLECT({Submission Date},{Annabelle Name}, "Annabelle",{Client Name (Pruning)}, @cell = [Client Name]@row))))

Make sure to update the correct column reference for the "Submission Date"

Craig Lemberger

Melissa, thank you.

I added that and the count comes to One, when it should be TWO. @Melissa Yamada

Craig Lemberger

I'd welcome more input if you can think of anythinng.

Melissa Yamada

@Craig Lemberger would you mind sharing some snippets of your sheet? Best if you can share which columns were you looking at (on your source sheet) and how you count it on your second sheet.

I think "Client Name (Pruning)" and "Annabelle Name" is causing the conflict that's why it is only counting 1.

Craig Lemberger

The source sheet is pretty simple: Date column (which represents a service date at a client site); there is a cell in the row that has key words, one of which is" annabelle". Another column callled "Annabelle Pruned" reads that cell with the key words and puts the word "annabelle" in the cell at the corresponding row. This column is called Annabelle Pruned and is also read on the second sheet.

How I count it is using the formula that you helped me with above.

The reason Annabelle may appear more than once is because there are other key words read which triggers copies of the row to be sent to the second sheet. Consequently if three rows get sent over because three key words were detected for the same date, annabelle gets over counted for example. @melissa yamada

Craig Lemberger

@Melissa Yamada

the client name pruning is a marker of distinction to correspond with the second sheet; I use the client name, and the date.

Craig Lemberger

@Melissa Yamada Your comment about the issue maybe around the annabelle name made me try something that worked!!! Thank you.

=IF(COUNTIF({Client Name (Pruning)}, [Client Name]@row) = 0, "", COUNT(DISTINCT(COLLECT({Date of Visit}, {Annabelle Name}, "Annabelle", {Client Name (Pruning)}, @cell = [Client Name]@row))))

I added Annabelle in quotes as I think the criterion range needed a more specific criterion.

Thank you for your probing and questioning the equation: it helped me to get where I needed. I am going to try this with the rest of the "key words"

Best,

Craig

Melissa Yamada

Happy to help @Craig Lemberger 😊

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, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.