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

Find more posts tagged with

Sheets

Formulas

Support

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.