Need help counting the number of unique values in a cell while referencing a second range

bhsiao_regcorp

That gives the same result

bhsiao_regcorp

Your formula gave the same result of 1.

Genevieve P.

Can you post what your range looks like when you click "Edit Reference"?

bhsiao_regcorp

bhsiao_regcorp

No, that formula shows the same result of 1

bhsiao_regcorp

That formula still returns a quantity of one.

Genevieve P.

Hi @bhsiao_regcorp

I'm unable to identify why the formula is not reading all the values. The last troubleshooting steps I would try is to use other functions to see if it has to do specifically with the COUNT(DISTINCT for some reason.

What results do you get with these:

=COUNT({Daily Sales Tracking Range 1})

In a multi-select cell:

=JOIN({Daily Sales Tracking Range 1}, CHAR(10))

bhsiao_regcorp

This formula =COUNT({Daily Sales Tracking Range 1}) does return the full quantity

Genevieve P.

Great! So this narrows it down to the Distinct function being unable to find separate values.

What did the JOIN formula show?

Can you try JOIN with DISTINCT:

=JOIN(DISTINCT({Daily Sales Tracking Range 1}), CHAR(10))