Count if Container Code is Unique

tchav
tchav ✭✭✭✭
edited 10/30/24 in Formulas and Functions

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!