Count Repeat Numbers

[ststern45]

Hi everyone,

I have 2 cell ranges and would like to calculate how many digits repeated.

For example,
Cells (1st range of numbers)
A1 = 0
B1 = 5
C1 = 4
D1 = 6
E1 = 7
F1 = 7

Cells (2nd range of numbers)

A2 = 0
B2= 4
C2 = 8
D2 = 6
E2 = 4
F2 = 4

So there are a total of 3 digits that repeated
0, 6, and 4

In addition, only count the value once.

For example,
1st range of digits
0 - 1 - 4 - 7 - 9 - 9

2nd range of digits
0 - 5 - 0 - 9 - 9 - 8

In the above example there are 2 digits that repeat 0 & 9. But the 9 appears twice in both ranges but I would only want the 9 counted once.

Thank you in advance

 

[mmhill]

You want to use a COUNTIF function and combine that with a MIN function

=COUNTIF($A$1:$F$2,4) ... this one counts the number of 4s in the range. Replace 4 with whatever number you are looking for.
=MIN(COUNTIF($A$1:$F$2,4),1) ... this one returns 0 if 4 is not present and 1 if it is, regardless of how many 4s are in the range

 

[kvsrinivasamurthy]

In B5 then copied across till blank cell is seen.

=IFERROR(INDEX($A$1:$F$1,AGGREGATE(15,6,COLUMN($A$1:$F$1)/((COUNTIF($A$2:$F$2,$A$1:$F$1)>0)*(COUNTIF($A$5:$A5,$A$1:$F$1)=0)),COLUMNS($B5:B5))),"")

A5 should be blank.

0​	5​	4​	6​	7​	7​
0​	4​	8​	6​	4​	4​
	B5	C5	D5	E5	F5
	0​	4​	6​

 

[shinigamilight]

Need a helper column in A3
A3: =IF(SUMPRODUCT(--($A$2:A2=A2))=1,IF(COUNTIFS($A$1:$F$1,A2)>1,1,COUNTIFS($A$1:$F$1,A2)),"")
G3: =SUM(A3:F3)



Bonus for excel 365 users requires no helper:
=SUMPRODUCT(--(COUNTIFS(A1:F1,UNIQUE(A2:F2,1))>0))

 

[Peter_SSs]

To calculate directly try this

22 12 11.xlsm
	A	B	C	D	E	F	G	H
1	0	1	4	7	9	9		2
2	0	5	0	9	9	8
Repeats

Cell Formulas
Range	Formula
H1	H1	=SUMPRODUCT(--(MATCH(A1:F1,A1:F1,0)=COLUMN(A1:F1)-COLUMN(A1)+1),--(COUNTIF(A2:F2,A1:F1)>0))

 

[ststern45]

Thank you everyone for all your help. Peter your formula worked.
I did not try the other suggestions.
Everyone is so amazing here at MrExcel.
Thank you, Thank you!!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.