Count Distinct Collect result is showing as 1

heyjay

Paul Newcome

It is because you have numbers and text mixed in the first range. Since the first value encountered is text it is only grabbing text, and there is only one distinct text value present.

Try this:

=COUNT(DISTINCT(COLLECT([Unit #]:[Unit #], [Unit #]:[Unit #], @cell + "" <> "", [BIN #]:[BIN #], @cell = 1)))

heyjay

@Paul Newcome - Im still getting 1, Im using this formula,

=COUNT(DISTINCT(COLLECT([Unit #]:[Unit #], [Unit #]:[Unit #], @cell + "" <> "", [BIN #]:[BIN #], @cell = 1)))

Paul Newcome

Ok. Try inserting a helper column (can be hidden after setting up) and enter this column formula:

=[Unit #]@row + ""

You would then reference this column instead of the Unit # column in your formula.

heyjay

Thats going to be an issue. I am not able to add another column. Thanks anyway, really appreciate your help.

G. Carpenter

Thank you, Paul. Your suggestion fixed my problem.