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


This question generated 18 answers. To proceed to the answers, click here.

This thread is current as of September 17, 2014.


For more resources for Microsoft Excel