Count if Container Code is Unique

Hello,

I have a metrics sheet that is referencing another sheet to count how many counts of a specific Hold/MECQI Reason

=COUNTIFS({Hold/MECQI Reason}, CONTAINS("incorrect container", @cell), {Hold Issuance Date}, >TODAY(-180), {Hold/MECQI Reason}, NOT(ISBLANK(@cell )))

I only want it to count if the corresponding "Container Code" is unique - I do not want it to count duplicate container codes. The container code column is in the sheet that it is currently referencing. Is there a way to do this? How would i use the Distinct formula in this?

I tried this, but it does not work

=COUNTIFS({Hold/MECQI Reason}, CONTAINS("incorrect container", @cell), DISTINCT({Container Code}:{Container Code}, {Hold Issuance Date}, >TODAY(-180), {Hold/MECQI Reason}, NOT(ISBLANK(@cell ))))

Right now it is pulling in the # 6 because that Hold reason is listed 6 times but all 6 of those have the same container code so I only want it to pull in once and have a count of 1

Find more posts tagged with

Formulas

Control Center

References

Accepted answers

All comments

Jennifer Kurtz

Hi!

I think you could use COUNT(DISTINCT(COLLECT( here —

The Collect function will allow you to specify whatever criteria you need to meet. So, you'd be counting only distinct codes that meet the criteria you specify.

tchav

Can you please provide me with what the whole formula would look like?

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