Formula To Check for Duplicates excluding 0 and empty cells

[APML]

Hi all, I have a range C2:C51. I would like a formula that tells me if there are any duplicates excluding empty cells or 0.
Note cells C2 to C51 have the following formula in them
C2=Watch!$B$11
C3=Watch!B38
ect
So I'm wanting to check the outcomes of the formulas.

 

[Legacy 143009]

D2:

=IF(AND(C2<>"",C2<>0,COUNTIF($C$2:$C$51,C2)>1),"There are duplicates", "")

 

[APML]

D2:

=IF(AND(C2<>"",C2<>0,COUNTIF($C$2:$C$51,C2)>1),"There are duplicates", "")

Thx for your help but I did try that and it doesn't work. It may just be looking at the formulas in the range which are all different, but when the formula returns the same answer then it doesn't seem to recognise it as a duplicate

 

[Legacy 143009]

Try this:

=IF(AND(C2<>"",C2<>0),IF(COUNTIF($C$2:$C$51,C2)>1,"There are duplicates","Unique value"), "")

 

[APML]

Thx for your help but I did try that and it doesn't work. It may just be looking at the formulas in the range which are all different, but when the formula returns the same answer then it doesn't seem to recognise it as a duplicate

Sorry got that wrong, it doesn't work but not for the reason I said. For some reason sometimes it's counting the 0 as duplicates other time it isn't

 

[APML]

Try this:

=IF(AND(C2<>"",C2<>0),IF(COUNTIF($C$2:$C$51,C2)>1,"There are duplicates","Unique value"), "")

So can't get that to work either

 

[Legacy 143009]

Maybe you sould put zero within quotes as "0". I have no other idea.

 

[Kerryx]

Can you load some of the data you are working with using the XL2BB addin, it makes resolving issues like this easier

 

[Fluff]

How about

=IF(MAX(COUNTIFS(C2:C51,C2:C51,C2:C51,"<>0"))>1,"Duplicates","Ok")

 

[APML]

How about

=IF(MAX(COUNTIFS(C2:C51,C2:C51,C2:C51,"<>0"))>1,"Duplicates","Ok")

Yes, that works. Thx for the help everyone, I really do appreciate the effort