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

[TABLE="width: 170"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]List 1[/TD]
[TD][/TD]
[TD]List 2[/TD]
[/TR]
[TR]
[TD]GF3[/TD]
[TD][/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]H2[/TD]
[TD][/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]NWL[/TD]
[TD][/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD][/TD]
[TD]P1C[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD][/TD]
[TD]P1R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]P2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]P2R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]R2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S1P1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S1P2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]WGO[/TD]
[/TR]
</tbody>[/TABLE]

 

[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