How do I build a formula that looks for duplicates that also ignores blanks or "N/A"?

Aubrey Jones

I'm trying to build a formula that counts if it finds a match so it will check a box but I don't want it to count blanks or when someone enters "N/A".

=(IF(COUNTIFS([Document ID #*]:[Document ID #*], [Document ID #*]@row) > 1, 1))

And no matter what I try I seem to break the formula.

sharkasits

@Aubrey Jones You can add an additional If around it.

=IF(AND([Document ID #*]@row != "", [Document ID #*]@row!="N/A"),IF(COUNTIFS([Document ID #*]:[Document ID #*], [Document ID #*]@row) > 1, 1,0),0)

Aubrey Jones

@sharkasits While the formula works it doesn't seem to be quite right because even if it finds a duplicate in the column it is just putting a zero for everything.

Aubrey Jones

I figured it out. =IF(COUNTIFS([Document ID #*]:[Document ID #*], [Document ID #*]@row, [Document ID #*]:[Document ID #*], "<>", [Document ID #*]:[Document ID #*], "<>N/A") > 1, 1, 0)