Excel Formula Calculation across corresponding values

[provoking]

I am trying to setup an Academic Plan for my son and have run into an issue. Column D is a drop down list.

What I want to do is simply calculate how many credits there are for the type of course requirement.

For instance, based on the mockup below there are 2 instances of GenEd and one is 2 credits and the other is 3 credits. So I want the formula to provide a sum of all instances of GenEd multiplied by the corresponding credits. and populate that formula in A19. Essentially return a value of 5. For Major Requirements it would return a value of 9.

I have familiarity with Countifs but having it do a calculation with corresponding values is baffling me. Any help would be appreciated. THANKS!

 

[Alex Blakenburg]

You are going to need a helper column or some sort of other conversion method.

Book3
	A	B	C	D
9	Credits	Class	Course #	Type
10	1	New Student Seminar
11	2	Ballet I		Major Req
12	3	Acting I		Major Req
13	3	MT Music Theory I		Major Req
14	1	Aural Skills I		Electives
15	1	Somatics and Self-Care for Musical Theatre		Major Req
16	2	Vocal Technique		GenEd
17	3	The History of Broadway		GenEd
18
19	5	General Education		GenEd
20	0	World Cultures
21	9	Major Requirements		Major Req
22	5	Electives		GenEd
Sheet1

Cell Formulas
Range	Formula
A19:A22	A19	=SUMIFS($A$10:$A$17,$D$10:$D$17,D19)

 

[Cubist]

Another option is to do a wildcard search on the first 3 characters. Based on the "mock-up" provided, it works, however might yield incorrect results depending on the exhaustive list of the Type dropdown.

=SUMIFS($A$10:$A$17,$D$10:$D$17,"*" & LEFT(B19,3) & "*")