UNIQUE function returning 0

[thewiseguy]

Hi all. I am using the UNIQUE function on a column full of text and the last unique value being returned is a 0. I have this occurring in 2 different UNIQUE functions.

How can I check the reference cells to see where this is coming from?

Any help is greatly appreciated.

 

[thewiseguy]

Depends on what columns you changed the formatting on.

lol ok so anything in column X that does not have a value is coming back as '0'. i had the hide option engaged. can i remove the zero and not hide it? i'm having trouble finding a way to do this with a formula.

 

[Fluff]

With this formula in X
=IF(N2="Yes","",Q2)

You should only get a 0 if N2 is not Yes & Q2 is empty

 

[thewiseguy]

With this formula in X
=IF(N2="Yes","",Q2)

You should only get a 0 if N2 is not Yes & Q2 is empty

yes, that is the exact case. column Q needs to be empty in certain instances. is there a workaround?

 

[Fluff]

In that case use
=SORT(UNIQUE(FILTER(INPUT!X2:X200,(INPUT!X2:X200 <>"")*(INPUT!X2:X200<>0),FALSE)))

 

[thewiseguy]

In that case use
=SORT(UNIQUE(FILTER(INPUT!X2:X200,(INPUT!X2:X200 <>"")*(INPUT!X2:X200<>0),FALSE)))

Thank you!