Count Values in 2 Cell Ranges

[ststern45]

1>Looking for a formula that will count how many numbers match range 1 (DO1:DX1) that are located in range 2 (DO2:DX2)
2>Count if only 1
Example:
DO1 = 13 DX2 = 13
DS1 = 30 DO2 = 30
DU1 = 33 DP2 = 33
DW1= 03 DS2 = 03

3>The formula in essence only counts values in DO1:DX1 only once. 13,10,30,33,00,03
4>The formula in essence only counts values in DO2:DX2 only once. 30,33,31,03,01,03,13

13 appears in both DO1:DX1 and DO2:DX2
10 appears in both DO1:DX1 and NOT in DO2:DX2
30 appears in both DO1:DX1 and DO2:DX2
33 appears in both DO1:DX1 and DO2:DX2
00 appears in both DO1:DX1 and NOT in DO2:DX2
03 appears in both DO1:DX1 and DO2:DX2

5>Final total = 4 values appear in cell range DO1:DX1 and DO2:DX2

Thank you in advance



DO1 = 13
DP1 = 10
DQ1 = 10
DR1 = 13
DS1 = 30
DT1 = 30
DU1 = 33
DV1 = 00
DW1 = 03
DX1 = 03

DO2 = 30
DP2 = 33
DQ2 =31
DR2 = 33
DS2 =03
DT2 = 01
DU2 =03
DV2 = 31
DW2 = 33
DX2 =13

 

[CA_Punit]

How about this

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	13	10	10	13	30	30	33	0	3	3
2	30	33	31	33	3	1	3	31	33	13
3
4
5	4
6
7
8
9
Sheet2

Cell Formulas
Range	Formula
A5	A5	=SUM(--(IFERROR(MATCH(FREQUENCY(A1:J1,A1:J1)^0*TRANSPOSE(A1:J1),TRANSPOSE(A2:J2),0),0)>=1))

 

[ststern45]

Thank you for all your help.
Works perfectly!!

 

[Peter_SSs]

Since you have Excel 365, this may also be applicable?

20 07 31.xlsm
	A	B	C	D	E	F	G	H	I	J
1	13	10	10	13	30	30	33	0	3	3
2	30	33	31	33	3	1	3	31	33	13
3
4
5	4
Count

Cell Formulas
Range	Formula
A5	A5	=COUNT(UNIQUE(FILTER(A1:J1,COUNTIF(A2:J2,A1:J1)),1))

 

[ststern45]

Thanks Peter.
Appreciate all you help

 

[Peter_SSs]

Thanks Peter.
Appreciate all you help

You're welcome. Thanks for the follow-up.