# vlookup and countif to find the pertaining letter for each student

#### N8DGR8

##### New Member
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>

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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>

Welcome to the forum!

Excel 2016 (Windows) 32 bit
ABCDEFGHIJ
1JohnA1L1P2JohnA
2MarkA2L1P1JohnL
3YvetteA0L1P3JohnP
4JohnP
5MarkA
6MarkA
7MarkL
8MarkP
9YvetteL
10YvetteP
11YvetteP
12YvetteP
Sheet2
Cell Formulas
RangeFormula
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)

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

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

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!

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.

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

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.

exactly

Replies
6
Views
711
Replies
1
Views
260
Replies
5
Views
319
Replies
0
Views
410
Replies
5
Views
401

1,196,056
Messages
6,013,156
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back