Count Multiple Numbers in Multiple Columns

Samantha S.

I've tried this a few different ways and can't seem to get the result that I want. I'm trying to determine if the serial numbers in a row (up to 3) also appear anywhere else in the sheet. Here is where I'm currently at, but it only works if there is only one serial in the row.

=IF(COUNTIFS([Serial #1]:[Serial #1], <>"", [Serial #1]:[Serial #1], [Serial #1]@row) + COUNTIFS([Serial #2]:[Serial #2], <>"", [Serial #2]:[Serial #2], [Serial #2]@row) + COUNTIFS([Accessory Serial #]:[Accessory Serial #], <>"", [Accessory Serial #]:[Accessory Serial #], [Accessory Serial #]@row) > 1, 1, 0)

Serial #1 may appear as a duplicate in either Serial #1 or Serial #2 columns. Same for Serial #2. Accessory Serial # will only appear as a duplicate in the Accessory Serial # column.

Any ideas on if I can accomplish this with one duplicate checkbox?

JamesB

@Sam H.

After trying different formulations, the issue I am finding it the best way for the formula to handle blank vs non-blank cells to move through the conditions while keeping the formula short. The test for a blank condition needs to occur, otherwise you will get a false positive on the blank cell condition as being a duplicate.

I believe the easiest way to do this is through hidden helper columns.

if you Create "Serial #1 Duplicate" with this formula:

=IF(NOT(ISBLANK([Serial #1]@row)), IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #1]@row) > 1, 1, IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #1]@row) >=1,1,0)))@Sam H.

"Serial #2 Duplicate" with this formula:

=IF(NOT(ISBLANK([Serial #2]@row)), IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #2]@row) > 1, 1, IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #2]@row) >= 1, 1, 0)))

"Accessory Serial Duplicate" with this formula:

=IF(NOT(ISBLANK([Accessory Serial #]@row)), IF(COUNTIF([Accessory Serial #]:[Accessory Serial #], =[Accessory Serial #]@row) > 1, 1, 0), 0)

Then you can create your "Duplicate Submission" column with this formula:

=IF(OR([Serial #1 Duplicate]@row = 1, [Serial #2 Duplicate]@row = 1, [Accessory Serial #]@row = 1), 1, 0)

JamesB

@Sam H.

Parsing through your ask if I understand it correctly, you can accomplish this with one Checkbox. Below is a formula that will create a checkbox based on duplicate data being found with the following conditions.

Serial #1 is Duplicated in Columns Serial #1 or Serial #2

Serial #2 is Duplicated in Columns Serial #1 or Serial #2

Accessory Serial # is Duplicated in Accessory Serial # Column

=IF(NOT(ISBLANK([Serial #1]@row)), IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #1]@row) > 1, 1, IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #1]@row) >= 1, 1)), IF(NOT(ISBLANK([Serial #2]@row)), IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #2]@row) > 1, 1, IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #2]@row) >= 1, 1)), IF(NOT(ISBLANK([Accessory Serial #]@row)), IF(COUNTIF([Accessory Serial #]:[Accessory Serial #], =[Accessory Serial #]@row) > 1, 1, 0))))

This formula says if Serial #1 column is not blank, look for a duplicate, if found check the box, if not found or is blank test Serial #2 column for same conditions, if found check the box, if not found or is blank test Accessory Serial # column for the condition.

Samantha S.

Hi James. You have my ask correct, but when I run that formula, I get two results that are incorrect:

JamesB

@Sam H.

I am not able to recreate the this in the sheet I utilized to test the formula. The only reasoning I can see is that the Accessory Serial number is what it is saying is duplicated. My assumption is that the system is not seeing the Accessory Serial number as "Blank" and thus it is saying it is duplicated because you have 2 cells in the same condition.

If you create a helper column for the Accessory Serial Number, you should be able to test this theory with the following formula.

=IF(NOT(ISBLANK([Accessory Serial #]@row)), IF(COUNTIF([Accessory Serial #]:[Accessory Serial #], =[Accessory Serial #]@row) > 1, 1, 0), 0)

Samantha S.

@JamesB

Unfortunately the blank Accessory is not the issue as I have many rows with blanks, but the formula only pulled these two.

JamesB

@Sam H.

If you look at my image below, I have broken out the formulations so we can see where duplicates are detected based on formula logic.

Rows 1 and 2 are shown from your image, but without cell links as I am not sure where you are linking data.

Row 3, shows that the information in Serial #1 is duplicated in either Serial #1 or #2 column

Row 4 shows that the information in Serial #2 is duplicated in either Serial #1 or #2 column

Rows 5&6 shows that the information is duplicated in the Accessory column

Rows 7&8 shows that the Accessory column formula is not referencing the Serial #1 or Serial #2 columns

This shows that the formula logic is correct. That is why I believe that the system is not seeing these 2 Accessory cells as blank and so it is executing the duplication formula and marking it as such.

Samantha S.

I tried entering fake serials into those fields and it still left the boxes checked. I also did another test where I duplicated a serial and it worked for one test and not the other.

Test 1: It check the box for one of the duplications but not the other. Same if I move the duplication to the Serial #1 field. Same for a duplicated Accessory.

Test 2: Duplicating Serial #1 worked no matter if I put it in a Serial #1 or Serial #2 field.

JamesB

@Sam H.

After reading through your summation, I see where I had created a gap in my formula not allowing it to get to the accessory equation. The adjustments I made to the formula should now work as needed.

=IF(NOT(ISBLANK([Serial #1]@row)), IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #1]@row) > 1, 1, IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #1]@row) >= 1,1,IF(NOT(ISBLANK([Serial #2]@row)), IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #2]@row) > 1, 1, IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #2]@row) >= 1, 1, IF(NOT(ISBLANK([Accessory Serial #]@row)), IF(COUNTIF([Accessory Serial #]:[Accessory Serial #], =[Accessory Serial #]@row) > 1, 1, 0))))

Samantha S.

That seemed to improve the accessory piece of the puzzle, but there is still something going on with only one box being checked for a duplicate:

JamesB

@Sam H.

After trying different formulations, the issue I am finding it the best way for the formula to handle blank vs non-blank cells to move through the conditions while keeping the formula short. The test for a blank condition needs to occur, otherwise you will get a false positive on the blank cell condition as being a duplicate.

I believe the easiest way to do this is through hidden helper columns.

if you Create "Serial #1 Duplicate" with this formula:

=IF(NOT(ISBLANK([Serial #1]@row)), IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #1]@row) > 1, 1, IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #1]@row) >=1,1,0)))@Sam H.

"Serial #2 Duplicate" with this formula:

=IF(NOT(ISBLANK([Serial #2]@row)), IF(COUNTIF([Serial #2]:[Serial #2], =[Serial #2]@row) > 1, 1, IF(COUNTIF([Serial #1]:[Serial #1], =[Serial #2]@row) >= 1, 1, 0)))

"Accessory Serial Duplicate" with this formula:

=IF(NOT(ISBLANK([Accessory Serial #]@row)), IF(COUNTIF([Accessory Serial #]:[Accessory Serial #], =[Accessory Serial #]@row) > 1, 1, 0), 0)

Then you can create your "Duplicate Submission" column with this formula:

=IF(OR([Serial #1 Duplicate]@row = 1, [Serial #2 Duplicate]@row = 1, [Accessory Serial #]@row = 1), 1, 0)

Samantha S.

That worked perfectly. Thank you so much for your help with this.