Count Unique Values based on another value

Brad Mulder

Hi all,

Is it possible to count unique values from two separate columns in one formula? In the attached photo I am trying to count the amount of unique "AssociationName" for each unique "CompanyName"? Basically, I want to know how many AssociationName's for each CompanyName.

Does that make sense? Thank you!!!

Mark Cronk

Hi Brad, This formula should return the number of unique association names under a company name.

=COUNT(DISTINCT(COLLECT(AssociationName:AssociationName, CompanyName:CompanyName, = [insert company name])))

So you don't have to create a formula for each company, create a column of distinct company names using the method in the link below. Then reference the company name [=CompanyName@row] with the formula above to count the unique association names under each company.

https://community.smartsheet.com/discussion/67208/how-to-get-distinct-values-from-a-column-then-shows-their-maximum-number

Does this help? I'm curious to see if there's an easier way.

Mark

Mark Cronk

Hi Brad, This formula should return the number of unique association names under a company name.

=COUNT(DISTINCT(COLLECT(AssociationName:AssociationName, CompanyName:CompanyName, = [insert company name])))

So you don't have to create a formula for each company, create a column of distinct company names using the method in the link below. Then reference the company name [=CompanyName@row] with the formula above to count the unique association names under each company.

https://community.smartsheet.com/discussion/67208/how-to-get-distinct-values-from-a-column-then-shows-their-maximum-number

Does this help? I'm curious to see if there's an easier way.

Mark

Brad Mulder

Mark,

Thank you! Worked perfectly! Much appreciated!