Counting Grades

[timetabler]

I am trying to count the number of grades a student has acheived that are above a grade C.

I have a named range called gcsegrades which have each grade and their relevant point value on another sheet. d=34 c=40 b=46 a=52

Rather than doing a a vlookup against each and every-cell is there a better procedure that can do this?
data example:

Smith, John, C, C, B, D, A would give the answer 4
Blggs, Fred, D, , E, B, A would give the answer 2

(note Fred Bloggs did not have a result for the second exam).

Many Thanks

 

[Richard Schollar]

Do you mean "C or above" rather than "above a grade C"? I can't see why John Smith would return a result of 4 otherwise...

Here's one way:

Excel Workbook
	A	B	C	D	E	F
1	Name	Grade1	Grade2	Grade3	Grade4	Grade5
2	Schollar	C	C	D	A	3
3	Jennings	B		A	D	2
4	Song	E	C	D	E	1
5	Tom					0
Sheet1

Excel 2002

Cell Formulas
Range	Formula
F2	=COUNTIF(B2:E2,"<=C")