# Duplicate Check Formula

Options
✭✭✭✭

Hello There,

I have created a column for duplicate check, It will flag the as red if both conditions are true

Condition 1 : CPR ID Duplicate

Condition 2 : Amount Duplicate

Formula which is giving me error:

=IF(AND(COUNTIF([CPR ID#]:[CPR ID#], [CPR ID#]1) > 1, IF(COUNTIF(Amount:Amount, Amount1) > 1, 1)))

I think i am not using AND function properly

• ✭✭✭✭✭✭
Options

Try this...

Get the Count first:

=COUNTIFS([CPR ID#]:[CPR ID#], [CPR ID#]1, Amount:Amount, Amount1)

Then say that if the count is greater than 1, flag. There is no need for the AND statement in this one.

=IF(COUNTIFS(............) > 1, 1)

=IF(COUNTIFS([CPR ID#]:[CPR ID#], [CPR ID#]1, Amount:Amount, Amount1) > 1, 1)

• ✭✭✭✭✭✭
Options

@Paul Newcome Is correct. That is how I would do it.

• ✭✭✭✭
Options

Thank you so much for the help @Paul Newcome & @L@123 . I just need to remove the cells where CPR ID# & Amount Column is either N/A or Blank

I tried with below but its not taking the later conditions - am i again missing AND? and if yes can you also show me how we can incorporate AND ( if it requires*)

=IF(COUNTIFS([CPR ID#]:[CPR ID#], [CPR ID#]@row, Amount:Amount, Amount@row) > 1, 1, IF([CPR ID#]@row, ="", 0, IF([CPR ID#]@row, ="N/A", 0, IF(Amount@row, ="", 0, IF(Amount@row, ="N/A"), 0))))

Its showing duplicates for below rows but its not the case since its either N/A or Blank :

• ✭✭✭✭✭✭
Options

Nested IF statements work from left to right and stop on the first true value. You are going to want to rearrange the order of your IF's so that the COUNTIFS is last.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!