count total unique values

Yashwant Singh

Hi team,

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

Alon Shachar

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")))

Alon Shachar

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")))

Yashwant Singh

thanks for your reply but formula is not working..

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

Alon Shachar

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"

Alon Shachar

Bassam Khalil

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:

Alon Shachar

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" )))

Alon Shachar

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")))