Duplicate data formula which references two values


Hello Community,

I am trying to find duplicate invoice numbers for the same client within two of my sheets.

I need to show if there have been any duplicate invoices for clients using two criteria but am unsure how to create the formula. I want this information to show in a checkbox form.

=IF(COUNTIF([Invoice #]:[Invoice #], [Invoice #]@row, Name:Name, Name@row>1, 1,0))

Appreciate any support on this.

Best wishes,



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You need to change from COUNTIF to COUNTIFS with the "S" on the end) to accommodate multiple range/criteria sets, and you will need to move one of the closing parenthesis from the very end to close out the COUNTIFS immediately after Name@row.

  • Chloe MB
    Chloe MB ✭✭✭✭

    @Paul Newcome

    Thank you for your help with this. I appreciate it. I am hoping you can assist further?

    The formula still isn't working. I have used the below formula but as you can see from the screenshot, it doesn't show the duplicate value. It shows 0.

    =COUNTIFS([Invoice #]:[Invoice #], [Invoice #]@row, Name:Name, Name@row > 1)

    Thank you in advance!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You needed to keep the IF portion, and you still need to move the closing parenthesis to immediately after Name@row.

    =IF(COUNTIFS([Invoice #]:[Invoice #], [Invoice #]@row, Name:Name, Name@row)>1, 1,0)

  • Jackie9
    Jackie9 ✭✭


    I have a similar situation but I cannot get the formulas to work. We have two separate sheets tracking property by using numerical property numbers. I would like "Property Sheet 1" to indicate when a duplicate property number has been entered by comparing the numbers from Column 1 on "Property Sheet 1" and Column 1 on"Property Sheet 2".

    I've used this type of formula: =IF((COUNT([Column A]1:[Column A]12)) = (COUNT(DISTINCT([Column A]1:[Column A]12))), "No Dups", "Duplicates exist") but it only gives me the "Duplicates Exist" no matter if the data is duplicated or not.

    I've tried this type of formula: =IF(COUNTIF(Store:Store, Store@row) > 1, 1, 0), but again everything is marked as "1" or checkmark (because I added a "Flag" Column to change to a "1" or "checkmark" if a duplicate number is found.

    What am I doing wrong here?


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Jackie9

    It sounds like you're wanting to compare data across sheets, but your formulas only have reference to in-sheet cells...there are no {cross sheet references}.

    See: Create cross sheet references to work with data in another sheet

    Try replacing the first "store" reference with the column in sheet 2:

    =IF(COUNTIF({Store Column Reference}, Store@row) > 1, 1, 0)

    If this hasn't helped, it would be useful if we could see screen captures of both sheet set-ups (but block out sensitive data or use demo data), with the formula open in the cell.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!