vlookup and countif to find the pertaining letter for each student

[N8DGR8]

I'm sure it's something simple, but it's just not coming to me. I'm assuming I need to combine a vlookup and countif so that i can get a result of how many A's L's or P's that each person has from the range in O1:P12 and place it next to their name in column N

any help would be appreciated, and if it will require VB, I'd rather even use a couple of formulas, that have to reference each other.

John		John	A
Mark		John	L
Yvette		John	P
		John	P
		Mark	A
		Mark	A
		Mark	L
		Mark	P
		Yvette	L
		Yvette	P
		Yvette	P
		Yvette	P

<tbody>
</tbody>

 

[Weazel]

maybe...

N1=SUM(COUNTIFS($O$1:$O$12,M1,$P$1:$P$12,{"A","L","P"}))

Row\Col	M​	N​	O​	P​
1​	John	4​	John	A
2​	Mark	4​	John	L
3​	Yvette	4​	John	P
4​			John	P
5​			Mark	A
6​			Mark	A
7​			Mark	L
8​			Mark	P
9​			Yvette	L
10​			Yvette	P
11​			Yvette	P
12​			Yvette	P

<tbody>
</tbody>

 

[AliGW]

Welcome to the forum!

How about this?

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J
1	John	A	1	L	1	P	2		John	A
2	Mark	A	2	L	1	P	1		John	L
3	Yvette	A	0	L	1	P	3		John	P
4									John	P
5									Mark	A
6									Mark	A
7									Mark	L
8									Mark	P
9									Yvette	L
10									Yvette	P
11									Yvette	P
12									Yvette	P
Sheet2

Cell Formulas
Range	Formula
C1	=COUNTIFS($I$1:$I$12,$A1,$J$1:$J$12,B1)
E1	=COUNTIFS($I$1:$I$12,$A1,$J$1:$J$12,D1)
G1	=COUNTIFS($I$1:$I$12,$A1,$J$1:$J$12,F1)

 

[N8DGR8]

that worked perfect Weazel, thank you, I knew it would be something simple that I would make difficult.

 

[N8DGR8]

thanks AliG, i like that one too, good idea to separate them for comparison.

 

[AliGW]

I see no point in doing it unless you separate them - you might just as well count pupil names if you don't want to know the break-down, since all results are one of the three!

 

[MattH1]

I see no point in doing it unless you separate them - you might just as well count pupil names if you don't want to know the break-down, since all results are one of the three!

Exactly this. If you're not separating them, you can just use a COUNT statement for each one of them. To get more specific (which is usually ideal in a scenario like this I assume), you would be better off separating them.

 

[Weazel]

unless there is a possibility of there being a letter other than A, L, or P

 

[AliGW]

I'm guessing we are dealing here with absent, late or present - there may be other codes, yes, but we don't know that from the sample data.

 

[Weazel]

exactly