Using countifs with a criteria to count distinct values

Shannon Darley

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!

David Joyeuse

Hi @Shannon Darley

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!

Giles Magee

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.

David Joyeuse

@Giles Magee

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.