VLOOKUP and SUMIF help

[MattCufre]

Hi,

I've been attempting to sort out a looukup table on the spreadsheet for one of the classes I teach. I've got to the stage where it all works apart from the totals. Because it's a BTEC class they are awarded "Pass" "Merit" or Distinction"s rather than a percentage score, but these all have numerical values (which I have in my lookup table on sheet 2).

Is there a way that I can set up my spreadsheet so that other teachers can open it type, for example, M (for merit) or P (for pass), and it returns a numerical value in my total column.

First time I tried using basic VLOOKUP functions, then I tried combined with SUMIF, but I'm really struggling and it's starting to hurt my head. Any help would be greatly appreciated!

BTEC Database no names.xlsx

^In my workbook I'm trying to populate column AE.

Thanks

Matt

[martindwilson]

use countif*lookup
=COUNTIF(L4:AD4,"di")*VLOOKUP("di",Sheet2!$I$9:$J$13,2,FALSE) replace di with a cell reference if needed containing di

[MattCufre]

THANK YOU. That was driving me mad.