Countif List 1 is in List 2

[p9j123]

I need help in creating the formula that will count how many from my List 1 are in List 2.


Something like this.


=countif(List2,List1)


List 2 is in Sheet3 Column A, this is a fixed list meaning it is not changing.


List 1 in in Sheet1 column J, it is active, it is changing from time to time.


My formula shoud be in A20, on the example below the formula should return 2 because R and S1 from List 1 is on List 2.

Thank you in advance

List 1		List 2
GF3		C1
H2		C2
NWL		P1
R		P1C
S1		P1R
		P2
		P2R
		R
		R2
		S1
		S1P1
		S1P2
		S2
		S3
		S4
		WGO

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Scott Huish]

Assuming your list is in Column A and B.

=SUM(--ISNUMBER(MATCH(A2:A18,B2:B18,0)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

Excel 2010
	A	B
1	List 1	List 2
2	GF3	C1
3	H2	C2
4	NWL	P1
5	R	P1C
6	S1	P1R
7		P2
8		P2R
9		R
10		R2
11		S1
12		S1P1
13		S1P2
14		S2
15		S3
16		S4
17		WGO
18
19
20	2
Sheet1

Cell Formulas
Range	Formula
A20	{=SUM(--ISNUMBER(MATCH(A2:A18,B2:B18,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[p9j123]

Thank you so much, it works like a charm