Count Data Using VLOOKUP

[pwill]

Hi,

Can anyone one help?

I want to use a formula in Cell L5 (Sheet1) to lookup Sam on (sheet2 column A) and count how many times the number 0 appears in column B using only the rows for Sam.

I want to be able to drag the formula down so that in Cell L6 (Sheet1) lookup Finds John on (sheet2 column A) and counts how many times the number 1 appears in column B using only the rows for John.

And drag the formula down again so that in Cell L7 (Sheet1) lookup Finds Peter on (sheet2 column A) and counts how many times the number 2 appears in column B using only the rows for Peter.

ie
I have put the count for each person (blue L5:L7) from sheet2

Sheet1

		J	K	L	M	N	O	P	Q	R	S
		Name	No.	Count
5		Sam	0	2
6		John	1	4
7		Peter	2	3
8
9
10
11
12

<tbody>
</tbody>

Sheet2

		A	B	C	D	E	F	G	H	I
2		Name
3		Sam	1	0	1	3	4
4		Sam	0	0			4
5		Sam	1	1	3	3
6		Sam	0	1		2	2
7		John	1	1	1		2
8		John	0	1			4
9		John	1	0		2	3
10		John	1	1	1
11		John	1	1	1	2	3
12		Peter	2	3
13		Peter	2	1	1	1	2
14		Peter	1	0	2		4
15		Peter	2			2
16		Peter	1	0	1	4	5
17
18
19
20

<tbody>
</tbody>

Any help would be apprieciated

pwill

 

[Aladin Akyurek]

In C5 of Sheet1 enter and copy down:

=COUNTIFS(Sheet2!A:A,$J5,Sheet2!B:B,$K5)

 

[pwill]

In C5 of Sheet1 enter and copy down:

=COUNTIFS(Sheet2!A:A,$J5,Sheet2!B:B,$K5)

Thanks Aladin Akyurek,

that works a treat, much apprieciated.

pwill

 

[Aladin Akyurek]

Thanks Aladin Akyurek,

that works a treat, much apprieciated.

pwill

You are welcome.