Flag duplicates based on multiple columns being the same

Options

Hi,

I have a "flag" column that I would like to use to flag rows that we consider duplicates. I got it to where it will flag if one piece of the criteria is duplicated, but not sure how to do the formula with multiple criteria met.

The purple fields need to all be the same to be considered a duplicate.

The formula I had working for one criteria is:

=IF([Supplier Invoice No]@row = "", 0, IF(COUNTIF([Supplier Invoice No]:[Supplier Invoice No], [Supplier Invoice No]@row) > 1, 1))

Any help would be much appreciated. Thank you!


Best Answer

  • John Pudar
    John Pudar ✭✭✭✭
    Answer ✓
    Options

    Hi @alexis.ray89371,

    Try changing to a COUNTIFS and then adding the other ranges and criteria:

    =IF([Supplier Invoice No]@row = "", 0, IF(COUNTIFS([Supplier Invoice No]:[Supplier Invoice No], [Supplier Invoice No]@row, [Invoice Amount]:[Invoice Amount], [Invoice Amount]@row, [Purchase Order Number]:[Purchase Order Number], [Purchase Order Number]@row) > 1, 1))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!