Thanks:  0
Likes:  0

Thread: Help with Counting Unique Duplicates

1. Help with Counting Unique Duplicates

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

2. Re: Help with Counting Unique Duplicates

Try this array formula:

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

3. Re: Help with Counting Unique Duplicates

Originally Posted by azumi
Try this array formula:

=(COUNTIF(B2:E3,"<"&B2:E3))
The formula returned "0".

Not sure what is wrong. Hmmnn

4. Re: Help with Counting Unique Duplicates

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

regards

5. Re: Help with Counting Unique Duplicates

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.

6. Re: Help with Counting Unique Duplicates

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

7. Re: Help with Counting Unique Duplicates

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

8. Re: Help with Counting Unique Duplicates

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.

9. Re: Help with Counting Unique Duplicates

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.

10. Re: Help with Counting Unique Duplicates

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.