=COUNT(DISTINCT(COLLECT formula returning 1

s_mawer

After much searching on this forum, I'm trying to use the above formula to count the number of unique names that have a box checked in another column. This is what I'm entering but each time it just comes back with the answer 1 which I know isn't correct:

=COUNT(DISTINCT(COLLECT({Assoc}, {Active}, =1)))

{Assoc} is a list of names of Associates - some of which are duplicated

{Active} is a checkbox column

I would like a count of unique names that are ticked as being Active.

I've tried the same formula with different criteria ranges (eg where the currency is "GBP") but I still keep coming back with the answer 1. Can someone please help me with where I'm going wrong!

Thanks

s_mawer

Thanks, have played around with it a bit more and I think the problem may have been that I imported the data in from Excel which was throwing Smartsheet off somehow. Copied and pasted the names in and out primary column and the formula magically started working!

Dale Murphy

@s_mawer The formula looks right, and if it isn't producing an error then it is probably okay structurally.

I would start by rebuilding the ranges from the other sheet; usually I discover I have somehow selected only part of a column or the wrong column altogether.

dm

s_mawer

Thanks, have played around with it a bit more and I think the problem may have been that I imported the data in from Excel which was throwing Smartsheet off somehow. Copied and pasted the names in and out primary column and the formula magically started working!