# Help with Counting Unique Duplicates

#### mandinko

##### New Member
Ok I will keep it simple with two rows. What I am trying to do is count unique duplicate values from one row into another row.

 Date Draw1 Draw2 Draw3 Draw4 9/16/2014 6 9 7 9 9/15/2014 6 1 9 3

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>
I am trying to count the number of repeat digits that occurred in draw 9/15 to 9/16. I am stuck with this formula and it gives me 3 which should be 2. This is my current formula and I am stuck right now. Please note I want to count the unique digits. For example the 6 and 9 was drawn on September 15 but it also came up on 9/16 too but it is counting the 9 twice but it should be once.

=SUM(IFERROR(SUM(1/COUNTIF(B21:E21,B20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,C20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,D20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,E20)),0)
)

Any help would be greatly appreciated.

Thanks

Calvin

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try this array formula:

=(COUNTIF(B2:E3,"<"&B2:E3))

Try this array formula:

=(COUNTIF(B2:E3,"<"&B2:E3))

The formula returned "0".

Not sure what is wrong. Hmmnn

When ENTER you need press CTRL-SHIFT-ENTER button together, do not ENTER alone

regards

Oh I forgot about that with array formulas. That works.. Thanks a million now let me replicate this throughout the spreadsheet. Saved me some headaches tonight. I really appreciate your help here.

I hate to come back at you one more time but I think something is wrong. I used the formula and it works for two rows but when I added a 3rd row and tried to copy the formula it is giving me a value of 4. Now 9/15 and 9/14 it should be 2 again instead of 4.. Any suggestions? I tried the absolute references even with \$b2:\$e4 or \$b\$2:\$e\$4 but it is not computing right. I removed the references and tried to copy down but it is not calculating right. Once again thanks for your help.

 Date Draw1 Draw2 Draw3 Draw4 9/16/2014 6 9 7 9 9/15/2014 6 1 9 3 9/14/2014 7 4 1 9

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

Here are the two formulas:

Cell F2 formula gives me "2"

=(COUNTIF(B2:E3,"<"&B2:E3))

But Cell F3 formula gives me "4"

=(COUNTIF(B3:E4,"<"&B3:E4))

And of course I hit CTRL-SHIFT-ENTER. Scratching my head here.. Thanks in advance

If there are no blank cells maybe something like this

 A​ B​ C​ D​ E​ F​ G​ 1​ Date​ Draw1​ Draw2​ Draw3​ Draw4​ Duplicates​ 2​ 09/16/2014​ 6​ 9​ 7​ 9​ 2​ 3​ 09/15/2014​ 6​ 1​ 9​ 3​ 2​ 4​ 09/14/2014​ 7​ 4​ 1​ 9​

Formula in G2 copied down
=SUMPRODUCT(1/COUNTIF(\$B2:\$E3,\$B2:\$E3),--((1/COUNTIF(\$B2:\$E3,\$B2:\$E3))<>1))

Hope this helps

M.

Thanks That is much cleaner. Do you have any reference docs I can go and read on how to use the "--" characters and "<>1". I am just trying to understand how it calculates. The evaluate function is hard to read on my screen. I only have a 15 inch monitor.

I think this one does what you want also... can enter it in row 3 and fill down.

{=SUM(IF(COUNTIF(B2:E3,B2:E3)=1,0,1/COUNTIF(B2:E3,B2:E3)))}

just realized it's pretty much the same that marcelo wrote- just wrote a little differently.

Replies
4
Views
166
Replies
1
Views
100
Replies
4
Views
307
Replies
4
Views
519
Replies
6
Views
170

1,203,242
Messages
6,054,350
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back