# Excel：使用XLOOKUP计算GPA

``````Course I     4.00      A
Course II    4.00      A
Course III   4.00      B
Course IV    4.00      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.

## 但…

``````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!