Getting Weighted GPA w/o Helper Columns

[JonXL]

Hello,

I've been looking to going back to school to get some additional certification and after downloading my college transcripts wanted to find my overall GPA (which is the grade-letter's numerical value weighted against the credit value for each course). I did this pretty easily with a helper column, but was interested to know if there's a way to do this with only one formula and no helper column. I tried using the following SUMPRODUCT formula, but it gave a #VALUE! error and I'm not really good-enough with SUMPRODUCT to troubleshoot what went wrong:

=SUMPRODUCT(Sheet1!C2:C46,VLOOKUP(Sheet1!F2:F46,Grades,2,FALSE))

I have two ranges in play - the first listing my courses, credits, and letter grade in each and the second listing each grade letter and it's GPA value as a lookup. The lookup range is called 'Grades', the course listing does not have a name.

Like I said, I can do this with a helper column and have already gotten my answer; my question here is just to explore how to do this without the helper column to help me learn something new.

The course listing:

Book1
	C	D	E	F
1	Earned Credit Hours	Non-Credit Hours	Grading Method	Grade
2	1	0	Letter Grade	B
3	3	0	Letter Grade	A
4	3	0	Letter Grade	A
5	3	0	Letter Grade	A
6	4	0	Letter Grade	A
7	0	0	Audit	AU
8	3	0	Letter Grade	A
9	3	0	Letter Grade	A
10	3	0	Letter Grade	A
11	3	0	Letter Grade	A
12	4	0	Letter Grade	A-
13	3	0	Letter Grade	A
14	3	0	Letter Grade	A
15	3	0	Letter Grade	A+
16	3	0	Letter Grade	B
17	3	0	Letter Grade	A
18	3	0	Letter Grade	A
19	1	0	Letter Grade	A
20	3	0	Letter Grade	B+
21	3	0	Letter Grade	B
22	3	0	Letter Grade	A
23	3	0	Letter Grade	A
24	4	0	Letter Grade	A
25	3	0	Letter Grade	B+
26	3	0	Letter Grade	A-
27	4	0	Letter Grade	A
28	2	0	Letter Grade	A
29	4	0	Letter Grade	A
30	3	0	Letter Grade	A
31	3	0	Letter Grade	A
32	3	0	Letter Grade	A-
33	3	0	Letter Grade	A
34	3	0	Letter Grade	A
35	3	0	Letter Grade	A
36	4	0	Letter Grade	A
37	4	0	Letter Grade	A
38	3	0	Letter Grade	A
39	4	0	Letter Grade	A
40	3	0	Letter Grade	A
41	3	0	Letter Grade	A
42	3	0	Letter Grade	A
43	3	0	Letter Grade	A
44	4	0	Letter Grade	A
45	3	0	Letter Grade	A
46	3	0	Letter Grade	A
Sheet1

The Grades range (A2:B15):

Book1
	A	B
1	Grade	GPA
2	A+	4.3
3	A	4
4	A-	3.6
5	B+	3.3
6	B	3
7	B-	2.6
8	C+	2.3
9	C	2
10	C-	1.6
11	D+	1.3
12	D	1
13	D-	0.3
14	F	0
15	AU	0
Grade_GPA

Appreciate any insights!

 

[Eric W]

Using VLOOKUP in a SUMPRODUCT usually doesn't work. Try SUMIFS instead:

Book1
	A	B	C	D	E	F	G	H
1	Grade	GPA	Earned Credit Hours	Non-Credit Hours	Grading Method	Grade		GPA
2	A+	4.3	1	0	Letter Grade	B		3.894852941
3	A	4	3	0	Letter Grade	A
4	A-	3.6	3	0	Letter Grade	A
5	B+	3.3	3	0	Letter Grade	A
6	B	3	4	0	Letter Grade	A
7	B-	2.6	0	0	Audit	AU
8	C+	2.3	3	0	Letter Grade	A
9	C	2	3	0	Letter Grade	A
10	C-	1.6	3	0	Letter Grade	A
11	D+	1.3	3	0	Letter Grade	A
12	D	1	4	0	Letter Grade	A-
13	D-	0.3	3	0	Letter Grade	A
14	F	0	3	0	Letter Grade	A
15	AU	0	3	0	Letter Grade	A+
16			3	0	Letter Grade	B
17			3	0	Letter Grade	A
18			3	0	Letter Grade	A
19			1	0	Letter Grade	A
20			3	0	Letter Grade	B+
21			3	0	Letter Grade	B
22			3	0	Letter Grade	A
23			3	0	Letter Grade	A
24			4	0	Letter Grade	A
25			3	0	Letter Grade	B+
26			3	0	Letter Grade	A-
27			4	0	Letter Grade	A
28			2	0	Letter Grade	A
29			4	0	Letter Grade	A
30			3	0	Letter Grade	A
31			3	0	Letter Grade	A
32			3	0	Letter Grade	A-
33			3	0	Letter Grade	A
34			3	0	Letter Grade	A
35			3	0	Letter Grade	A
36			4	0	Letter Grade	A
37			4	0	Letter Grade	A
38			3	0	Letter Grade	A
39			4	0	Letter Grade	A
40			3	0	Letter Grade	A
41			3	0	Letter Grade	A
42			3	0	Letter Grade	A
43			3	0	Letter Grade	A
44			4	0	Letter Grade	A
45			3	0	Letter Grade	A
46			3	0	Letter Grade	A
Sheet2

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(C2:C46*SUMIFS(B2:B15,A2:A15,F2:F46))/SUM(C2:C46)

 

[JonXL]

Using VLOOKUP in a SUMPRODUCT usually doesn't work. Try SUMIFS instead:

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(C2:C46*SUMIFS(B2:B15,A2:A15,F2:F46))/SUM(C2:C46)

That's awesome! Thank you for teaching me something new.

 

[Eric W]

Happy to help!