Duplicate data formula which references two values

Chloe MB

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

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

@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

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

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

Genevieve P.

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