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,

Chloe

Answers

  • 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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jackie9
    Jackie9 ✭✭

    Hi,

    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?


    Jackie

  • 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.

    Thanks!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!