SUM Text based on another column

[JackRegan]

Hi

Trying to turn a bunch of A-D ratings into numerical results (without a helper column) based on other data in the table.

A=4, B=3, C=2, D=2

In laymans

If control = blue then calculate [BASIC|STANDARD|EXTREME|HAWKINS] category score.​

Control & Category are fixed and Rating will always be A-D.
A control will always have one of the categories

CONTROL	CATEGORY	RATING
BLUE	BASIC	C
BLUE	STANDARD	A
BLUE	BASIC	A
ORANGE	HAWKINS	B
ORANGE	EXTREME	D
ORANGE	BASIC	C
PINK	STANDARD	C
PINK	HAWKINS	B
PINK	BASIC	A
BASIC TOTAL - BLUE
STANDARD TOTAL - BLUE
EXTREME TOTAL - BLUE
HAWKINS TOTAL - BLUE
BASIC TOTAL - ORANGE
STANDARD TOTAL - ORANGE
EXTREME TOTAL - ORANGE
HAWKINS TOTAL - ORANGE
BASIC TOTAL - PINK
STANDARD TOTAL - PINK
EXTREME TOTAL - PINK
HAWKINS TOTAL - PINK

Example :
BASIC BLUE TOTAL = C,A,[2,4] = 6

Not sure I have done a great job of explaining ... but welcome any input.

 

[Fluff]

Glad we could help & thanks for the feedback.