# count total unique values

Hi team,

I have 3 columns, and want to get count of unique values as par below.

• COUNT(DISTINCT(COLLECT(ID,NAME,"A",STATE, "UP")))

COUNT(DISTINCT(COLLECT(ID,NAME,"A",STATE, "UK")))

COUNT(DISTINCT(COLLECT(ID,NAME,"B",STATE, "UP")))

COUNT(DISTINCT(COLLECT(ID,NAME,"B",STATE, "UK")))

• edited 06/09/21

You can also use:

COUNT(DISTINCT(COLLECT(ID,NAME,"A",STATE, "UP"), COLLECT(ID,NAME,"A",STATE, "UK")))

COUNT(DISTINCT(COLLECT(ID,NAME,"B",STATE, "UP"), COLLECT(ID,NAME,"B",STATE, "UK")))

OR

COUNT(DISTINCT(COLLECT(ID,NAME, "A")))

COUNT(DISTINCT(COLLECT(ID,NAME, "B")))

collect(range, criteria range1, criteria, criteria range2, criteria)

• edited 06/09/21

You are correct....

ID , Name, State - Select the columns as range and do not forget to put = before the formula:

=COUNT(DISTINCT(COLLECT(NAME,NAME, "A"))) --- This will result the Distinct count of "A"

=COUNT(DISTINCT(COLLECT(NAME, NAME, "B"))) ---- This will result the Distinct count of "B"

• ✭✭✭✭✭✭

Hi @Yashwant Singh

Hope you are fine, it's better if you have a table of unique criteria such as ( Name contains A,B,C ..) & Status ( UP, UK, FA ..Atc) to help you in your formula for the sample you show in your screenshot please try the following formulas and it will work:

For A Count =COUNT(DISTINCT(COLLECT(ID:ID, NAME:NAME, Unique@row, STATE:STATE, "UP")))

For B Count =COUNT(DISTINCT(COLLECT(ID:ID, NAME:NAME, Unique@row, STATE:STATE, "UP")))

the following screenshot shows the result:

bassam.khalil2009@gmail.com

• I Would be happy to ZOOM with you to discuses this - there are several options depends on what you need to distinct count based on a specific range.

For example the number of customers that bought something in Q1 of 2021

Range - Customers.

Range_Criteria - Q (Column)

Criteria - "Q1"

=Count(Distinct(Collect(Customers,Q,"Q1")))

If i need to add another parameter for the count like "paid and invoiced" (Column) "YES" or "NO":

=Count(Distinct(Collect(Customers,Q,"Q1" ,paid and invoiced, "YES" )))

• edited 06/09/21

Hi @Bassam Khalil ✭

Your suggestion will only work if the condition is based on "UP" or "UK"

If you insert B instead of one of the "A" it will show 2 because it will have 2 distinct names

For only the distinct count of A or B without the second Criteria:

For A Count =COUNT(DISTINCT(COLLECT(NAME:NAME, NAME:NAME, "A")))

For B Count =COUNT(DISTINCT(COLLECT(NAME:NAME NAME:NAME, "B")))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!