Count unique suppliers active / not active



I'm trying to count the number of unique Third parties on a contract tracker sheet.

I want to be able to count the unique numbers of Third parties by Contract Group

So in the snip below:

  • Supplier 3 and Supplier 4 would equal a count of 2 Not Active.
  • Active would equal a count of 1
  • Requested would equal a count of 2
  • Future would equal a count of 2

I've tried various permutations using COUNT(DISTINCT(COLLECT…..

e.g., =COUNT(DISTINCT(COLLECT([Third Party]:[Third Party], [Contract Group]:[Contract Group], <>"2-Active")))

…but it is the bit where the suppliers are various Contract Groups that is the sticking point.

As ever, any thoughts would be great.


  • Mark.poole
    Mark.poole ✭✭✭✭✭


    Are you putting this information in a metric sheet? Or on the current sheet?

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • A_C
    A_C ✭✭

    Hi Mark,

    I'm going to put it in a metric sheet, so using the ranges, but also would like to solve it in it's own sheet, so I can have the option of summaries etc.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!