Comparing Repeating Cells

[dkenebre]

How do you express a formula for the following examples

If A2, B2 and C2 all cells with same numbers as A1, B1 and C1,
in any order, then D1 equals 3, (ie 3,,7,,2 followed by 3,,2,,7)
but
If A2, B2 and C2 have only 2 cells which is the same as A1, B1 and C1,

in any order, then D1 equals 2,
(ie 3,,7,,2 followed by 2,,3,,4) or (ie 3,,7,,2 followed by 3,,7,,7)
but
If A2, B2 and C2 have only 1 cell which is the same as A1, B1 and C1,
in any order, then D1 equals 1,
(ie 3,,7,,2 followed by 0,,2,,4) or (ie 3,,7,,2 followed by 3,,3,,8)
or ie 3,,7,,2 followed by 2,,2,,2
but
If A2, B2 and C2 have no cells which are the same as A1, B1 and C1, in
any order, then D1 equals 0,
ie 3,,7,,2 followed by 4,,1,,9

 

[Michael]

As long as yuo are always comparing against row 1 the
following will work.
put the following formula
in G1 =IF(C2=$A$1,1,IF(C2=$B$1,1,IF(C2=$C$1,1,0)))
in F1 =IF(B2=$A$1,1,IF(B2=$B$1,1,IF(B2=$C$1,1,0)))
in E1 =IF(A2=$A$1,1,IF(A2=$B$1,1,IF(A2=$C$1,1,0)))
in D1 =SUM(E1:G1)

There may be a better way but this should work as a quick
fix. You can hide columns E to G

Michael