Excel: Calculating GPAs with XLOOKUP


I am making an Excel spreadsheet to calculate grade-point averages (GPAs). In column A, I have the course names; in column B, the corresponding numbers of credits; and in column C, the letter grades.

Course I     4.00      A
Course II    4.00      A
Course III   4.00      B
Course IV    4.00      B

I have the grade system on a separate sheet with the letter grades in column A and their corresponding grade points in column B.

A     4.00
A–    3.67
B+    3.33
B     3.00
B–    2.67
C+    2.33
C     2.00
D     1.00
F     0.00
S     —
AU    —
I     —
W     —
NGR   —

In the simplest case, I calculate GPA as the ratio of the sum of the products of the credits and grade points to the sum of the credits. In the example above, that would be (4.00*4.00+4.00*4.00+4.00*3.00+4.00*3.00)/(4.00+4.00+4.00+4.00). In Excel, I can make a column D that has the weighted grade points: =B1*XLOOKUP(C1,Grades,GradePoints), where Grades and GradePoints are A1:A9 and B1:B9, respectively, on Sheet 2. Each cell in column D takes the grade from column C, looks up the corresponding grade points, and multiplies by the number of credits. Then I can sum column D (=SUM(D1:D4)) and divide it by the number of credits (=SUM(B1:B4)), and voilà, we have a GPA.

BUT…

I have a problem when there’s an S (satisfactory), a W (withdrawn), an I (incomplete), or other designations that don’t have grade points. Let’s replace the first example with this:

Course I     4.00     A
Course II    0.50     S
Course III   4.00     A
Course IV    4.00     B
Course V     4.00     B

When I’m calculating the grade points, I want to exclude course II. Naturally, I would want to use SUMIF or SUMIFS, but I can’t figure out what the criteria would be to sum only the credits that have a corresponding grade in the named range Grades. If I have =ISNUMBER(XMATCH(C1,Grades)), I get TRUE or FALSE appropriately, but I don’t know how to use that in SUMIF(S). I have also tried using INDEX/MATCH without success. For the grade points in column D, I could have XLOOKUP return zero if the grade isn’t in the named range Grades, e.g., =B2*XLOOKUP(C2,Grades,GradePoints,0) would return zero since S is not in Grades. Finally, I don’t actually want to have an explicit column D or hidden cells either. I just want to show the GPA. Any help would be greatly appreciated!