Excel Formula Help

[ststern45]

Need help with a formula.

Trying to calculate how many digits repeated from a previous range but NOT in the same position.

For example,

A1 = 7
B1 = 8
C1 = 3
D1 = 8
E1 = 9

A2 = 1
B2 = 6
C2 = 3
D2 = 3
E2 = 5

In the above example the only digit that repeats but not in the same column letter would be the number 3. Appeared in cell C1 and also D2. Even though it appears in cell C1 and C2 I do NOT want to calculate this.

Also the minimum value has to be 1 per digit.
So if a 3 was in cell E2 the total would still be 1.

For example:

A1 = 5
B1 = 5
C1 = 8
D1 = 7
E1 = 5

A2 = 1
B2 = 2
C2 = 9
D2 = 3
E2 = 5

The count would be 1 even though a 5 appears in cell A1 and E1 and 5 appears in cell E2.

Thanks in advance

 

[Scott Huish]

In C1:
=AND(COUNTIF(A:A,B1),A1<>B1)+0

Copy down.

Excel Workbook
	A	B	C
1	7	1	0
2	8	6	0
3	3	3	0
4	8	3	1
5	9	5	0
Sheet1

 

[ststern45]

Scott,

Thank you very much. This is exactly what I needed.
Very much appreciated!!
Steve