count total unique values
Hi team,
I have 3 columns, and want to get count of unique values as par below.
Answers
-
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")))
-
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")))
-
thanks for your reply but formula is not working..
collect(range, criteria range1, criteria, criteria range2, criteria)
-
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
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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" )))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!