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]

How about

+Fluff 1.xlsm
	A	B	C
1	CONTROL	CATEGORY	RATING
2	BLUE	BASIC	C
3	BLUE	STANDARD	A
4	BLUE	BASIC	A
5	ORANGE	HAWKINS	B
6	ORANGE	EXTREME	D
7	ORANGE	BASIC	C
8	PINK	STANDARD	C
9	PINK	HAWKINS	B
10	PINK	BASIC	A
11
12	Blue	Basic	6
13	Blue	Standard	4
14	Blue	Extreme	0
15	Blue	Hawkins	0
16
17	Orange	Basic	2
18	Orange	Standard	0
19	Orange	Extreme	1
20	Orange	Hawkins	3
21
22	Pink	Basic	4
23	Pink	Standard	2
24	Pink	Extreme	0
25	Pink	Hawkins	3
Lists

Cell Formulas
Range	Formula
C12:C15,C22:C25,C17:C20	C12	=SUMPRODUCT(($A$2:$A$10=A12)*($B$2:$B$10=B12)*(SWITCH($C$2:$C$10,"A",4,"B",3,"C",2,"D",1)))

 

[FormR]

Hi, here's another option you could try.

Book7
	A	B	C
1	CONTROL	CATEGORY	RATING
2	BLUE	BASIC	C
3	BLUE	STANDARD	A
4	BLUE	BASIC	A
5	ORANGE	HAWKINS	B
6	ORANGE	EXTREME	D
7	ORANGE	BASIC	C
8	PINK	STANDARD	C
9	PINK	HAWKINS	B
10	PINK	BASIC	A
11
12	Blue	Basic	6
13	Blue	Standard	4
14	Blue	Extreme	0
15	Blue	Hawkins	0
16
17	Orange	Basic	2
18	Orange	Standard	0
19	Orange	Extreme	1
20	Orange	Hawkins	3
21
22	Pink	Basic	4
23	Pink	Standard	2
24	Pink	Extreme	0
25	Pink	Hawkins	3
Sheet1

Cell Formulas
Range	Formula
C22:C25,C17:C20,C12:C15	C12	=SUMPRODUCT(COUNTIFS($A$2:$A$10,A12,$B$2:$B$10,B12,$C$2:$C$10,{"A","B","C","D"}),{4,3,2,1})

 

[JackRegan]

Thank you!

Working as an example but struggling to apply it to my data. Tables, and data across sheets seem to be causing it so will wind it all back .. But the formula works.

Paul

 

[Fluff]

To run it over 2 sheets you need to add the sheet name like

+Fluff 1.xlsm
	A	B	C
1	CONTROL	CATEGORY	RATING
2	BLUE	BASIC	C
3	BLUE	STANDARD	A
4	BLUE	BASIC	A
5	ORANGE	HAWKINS	B
6	ORANGE	EXTREME	D
7	ORANGE	BASIC	C
8	PINK	STANDARD	C
9	PINK	HAWKINS	B
10	PINK	BASIC	A
Sheet1

+Fluff 1.xlsm
	A	B	C
1
2	Blue	Basic	6
3	Blue	Standard	4
4	Blue	Extreme	0
5	Blue	Hawkins	0
6
7	Orange	Basic	2
8	Orange	Standard	0
9	Orange	Extreme	1
10	Orange	Hawkins	3
11
12	Pink	Basic	4
13	Pink	Standard	2
14	Pink	Extreme	0
15	Pink	Hawkins	3
Lists

Cell Formulas
Range	Formula
C2:C5,C12:C15,C7:C10	C2	=SUMPRODUCT((Sheet1!$A$2:$A$10=A2)*(Sheet1!$B$2:$B$10=B2)*(SWITCH(Sheet1!$C$2:$C$10,"A",4,"B",3,"C",2,"D",1)))

 

[JackRegan]

To run it over 2 sheets you need to add the sheet name like

+Fluff 1.xlsm
	A	B	C
1	CONTROL	CATEGORY	RATING
2	BLUE	BASIC	C
3	BLUE	STANDARD	A
4	BLUE	BASIC	A
5	ORANGE	HAWKINS	B
6	ORANGE	EXTREME	D
7	ORANGE	BASIC	C
8	PINK	STANDARD	C
9	PINK	HAWKINS	B
10	PINK	BASIC	A
Sheet1

+Fluff 1.xlsm
	A	B	C
1
2	Blue	Basic	6
3	Blue	Standard	4
4	Blue	Extreme	0
5	Blue	Hawkins	0
6
7	Orange	Basic	2
8	Orange	Standard	0
9	Orange	Extreme	1
10	Orange	Hawkins	3
11
12	Pink	Basic	4
13	Pink	Standard	2
14	Pink	Extreme	0
15	Pink	Hawkins	3
Lists

Cell Formulas
Range	Formula
C2:C5,C12:C15,C7:C10	C2	=SUMPRODUCT((Sheet1!$A$2:$A$10=A2)*(Sheet1!$B$2:$B$10=B2)*(SWITCH(Sheet1!$C$2:$C$10,"A",4,"B",3,"C",2,"D",1)))

Thank you .. I managed to work past that. Now bumping into the original data uses named ranges (in a table) which i was trying to apply to your formula. That breaks things.

 

[JackRegan]

Thank you .. I managed to work past that. Now bumping into the original data uses named ranges (in a table) which i was trying to apply to your formula. That breaks things.

It's breaking on empty cells anywhere in the sumproduct arrays'- which the named ranges all have as they are for the entire column.

Can

 

[FormR]

It's breaking on empty cells

Hi, the option in post#3 should work fine with blank cells.

 

[Fluff]

In that case you need to add a default to the Switch like

=SUMPRODUCT((CONTROL=A2)*(CATEGORY=B2)*(SWITCH(RATING,"A",4,"B",3,"C",2,"D",1,0)))

 

[JackRegan]

Sorted. Thanks both for your awesome help .. I'd have been hacking away for weeks.