Using countifs with a criteria to count distinct values
Hello,
I am trying to count the number distinct values with status colors that are red, yellow, or green. We have the same property IDs with multiple line items. Therefore, I am trying to count multiple property IDs as one distinct value if they have a status of red, yellow, or green.
The logic would be count distinct values (property IDs) if the status if xxx.
Thank you!
Answers
-
Try something like:
=JOIN(DISTINCT(COLLECT({Property ID Range}, {Status Range}, "Red")),CHAR(10))
This will return all properties with Red status.
Just adapt the color for Yellow and Green status.
Hope it helped!
-
Hi @David Joyeuse,
Forgive me if I am misunderstanding something but would your solution that not just give @Shannon Darley a list of the Property ID's that have a red status against them, as opposed to counting them?
My reading of her question is that Shannon is looking for a count of the number of Property ID's that have red (yellow and/or green) tasks against them. How does one then get a count of the Property ID's from your formula?
I have been playing around trying to figure this one, as I do not have a lot of experience with the DISTINCT function in Smartsheet and am getting stuck as well.
The only way I could think of doing it would be to flag the Parent row (assuming the Property ID's are the parent row and the tasks are CHILDREN) in some way, if there is a red task within it and then count those parent rows which have this flag, but I realise that this then doesn't allow for yellow and green tasks, unless you add flag columns for those as well, which you might want to avoid - or at least the yellow ones, as everything else would be green only presumably. 😅
I look forward to hearing your thoughts.
-
You're right, seems I missed the Counting part of what @Shannon Darley is trying to achieve here.
=COUNT(DISTINCT(COLLECT({Property ID Range}, {Status Range}, "Red")))
Should do it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!