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