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
Answers
-
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.
-
Can you please provide me with what the whole formula would look like?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!