Help with Counting Unique Duplicates

mandinko

New Member
Joined
Sep 17, 2014
Messages
24
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.

DateDraw1Draw2Draw3Draw4
9/16/20146979
9/15/20146193

<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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
When ENTER you need press CTRL-SHIFT-ENTER button together, do not ENTER alone

regards
 
Upvote 0
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.
 
Upvote 0
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.

DateDraw1Draw2Draw3Draw4
9/16/20146979
9/15/20146193
9/14/20147419

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top