Count Duplicates Only if the Field is NOT Blank

DKManley

I am struggling to get the formula right to solve this problem. My formula can count if there are duplicates in this field, but it also counts it if the field is blank (because many rows won't have a value in this field). Can anyone help me add the "if it is not blank" piece so i only get a 1 if there is a value in the TFPhoneNo field?

=IF(COUNTIF(TFPhoneNo:TFPhoneNo, TFPhoneNo@row) > 1, 1)

I appreciate it.

Hollie Green

=IF(TFPhoneNo@row,"","",IF(COUNTIF(TFPhoneNo:TFPhoneNo, TFPhoneNo@row) > 1, 1))

Soumitra Bhowmick

Try this

=IF(AND(COUNTIF(TFPhoneNo:TFPhoneNo, TFPhoneNo@row) > 1, NOT(ISBLANK(TFPhoneNo@row))), 1)

Hollie Green

=IF(TFPhoneNo@row,"","",IF(COUNTIF(TFPhoneNo:TFPhoneNo, TFPhoneNo@row) > 1, 1))

Soumitra Bhowmick

Try this

=IF(AND(COUNTIF(TFPhoneNo:TFPhoneNo, TFPhoneNo@row) > 1, NOT(ISBLANK(TFPhoneNo@row))), 1)

DKManley

Soum - that worked perfectly, thank you so much!