Count unique values if a criteria is met
Hello,
I tried to look to questions related to this, but honestly I don't understand the logic.
I need to count the unique values of the column "Supplier Name" only if they have an "Owner" assigned.
Best Answer
-
You can use the DISTINCT function to look for Unique Values, and the COLLECT function to filter out any rows that say "unassigned"... and then wrap this whole formula in a COUNT!
Try this:
=COUNT(DISTINCT(COLLECT([Supplier Name]:[Supplier Name], [Alten Owner]:[Alten Owner], <>"Unassigned")))
The COUNT(DISTINCT counts how many unique values there are in a Range.
The COLLECT function helps us create that range based on criteria in other columns. The way COLLECT works is that you list the column you want to count first: [Supplier Name]:[Supplier Name]
Then you list the column that has your conditions: [Alten Owner]:[Alten Owner]
And the criteria you're looking for in that conditional column... in your case that's "not unassigned" so we can use <> to say "not equal to": <>"Unassigned"
Let me know if that works for you and if the logic makes sense!
Cheers,
Genevieve
Answers
-
You can use the DISTINCT function to look for Unique Values, and the COLLECT function to filter out any rows that say "unassigned"... and then wrap this whole formula in a COUNT!
Try this:
=COUNT(DISTINCT(COLLECT([Supplier Name]:[Supplier Name], [Alten Owner]:[Alten Owner], <>"Unassigned")))
The COUNT(DISTINCT counts how many unique values there are in a Range.
The COLLECT function helps us create that range based on criteria in other columns. The way COLLECT works is that you list the column you want to count first: [Supplier Name]:[Supplier Name]
Then you list the column that has your conditions: [Alten Owner]:[Alten Owner]
And the criteria you're looking for in that conditional column... in your case that's "not unassigned" so we can use <> to say "not equal to": <>"Unassigned"
Let me know if that works for you and if the logic makes sense!
Cheers,
Genevieve
-
Genevive, you saved me! It totally works.
Also, with the detailed explanation, I was able to create another extra formulas that I needed.
Thank you so much!
-
So glad to hear that! Happy to help 😊
-
Hi,
I tried using the COUNT(DISTINCT(COLLECT function to determine how many unique values based on a Date column. I need to count the unique Batch Numbers that were Received on Date = TODAY.
This is the formula I came up with: =COUNT(DISTINCT(COLLECT({Batch Number}, {Date: Received}, TODAY())))
It returns 1, which I know is incorrect. It seems to work when I add days before or after TODAY. For example, when I count the unique Batch Numbers Received TODAY(-4), it returns the correct number.
Any ideas?
-
Is it possible that there is a timezone difference which could be the cause of this? The TODAY function is set to UTC time.
-
@Genevieve P. I just want to say you are amazing
what a formula, i just used it for my own purposes and it's working!
genuine question, how can i develop this knowledge on smartsheet to use formulas like this please?
-
Hi @Maz Uddin
Haha, thank you! I'm so glad the formula worked for you as well.
I've learned a lot right here in the Community, there are a lot of solutions built here by formula gurus much wiser than I!
There are a number of other resources as well:
- My personal favourite is the Formula Template sheet that you can create in your Smartsheet account so you can see how the different functions work in a sheet in real-time.
- Then there are the Formula Webinars: Formulas webinar series and Smartsheet Formula Best Practices
- And of course the wonderful eLearning Courses in the Smartsheet University (learn more about the University here).
Cheers,
Genevieve
-
Hi @Genevieve P. wow, thank you for all this information. You are an amazing person!
-
Amazing instruction! It saved me!!!
Thank you very much for sharing!!!!!
-
Looked good, but no matter the Criteria, it only returns a "1". Even if my criteria is something that is not part of my range.
-
Hi Todd,
Would you be able to explain further? It would be helpful to see the exact formula you're using, along with the sheet you're referencing (but please block out sensitive data).
Cheers,
Genevieve
-
I'm working on a sheet summary field. I have two columns. [Area] and [City] values for [Area] are region based Northeast, South, West, etc..... it is a Text/Number field. There is a one to many relationship between [Area] and [City] also formatted as Text/Number. I'm trying to count how many unique cities there are in a particular Area. I would put an example of a formula, but in my mind I've attempted every conceivable combination of COUNTIF, DISTINCT, HAS, COLLECT, JOIN, SUMIF, MATCH, etc.... Clearly I have not tried them all, but I do believe I have received the full range of rejection messages that you have provisioned in your system. 🙃
-
Thanks for clarifying - it sounds like the formula above should work for you!
For example, if you're looking within "North", try something like:
=COUNT(DISTINCT(COLLECT(City:City, Area:Area, "North")))
Let me know if this gives you an error or an incorrect result. If you're still getting 1, could you post a screen capture of a sheet with test-data (like mine above) showing your configuration / set-up for these two columns?
Thanks,
Genevieve
-
Thank you, that did work........ I really appreciate the help.
-
Wonderful! I'm glad we could help you out 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!
