Sumproduct if match

[icytuvi]

Hello there,

I have a excel problem that I need your support in solving this.

- We have products using paint mixture which have blending ratio change over seasons (summer & winter) (table 1)
- The unit cost of paints are given by month corresponding to specific season (table 2).
- Now to calculate the cost of product (table 3)

Could you please suggest if there is any formula that help me not to do the multiplication manually one by one?

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Table 1: Color blending ratio of products
3	Blending %	Winter			Summer
4		Red	Yellow	Green	Red	Yellow	Green
5	Product 1	25%	30%	45%	50%	30%	20%
6	Product 2	30%	28%	43%	55%	28%	18%
7	Product 3	35%	25%	40%	60%	25%	15%
8	Product 4	40%	23%	38%	65%	23%	13%
9	Product 5	45%	20%	35%	70%	20%	10%
10	Product 6	50%	18%	33%	75%	18%	7%
11
12	Table 2: Cost of paint
13	Unit cost	Winter	Winter	Winter	Summer	Summer	Summer	Summer	Summer	Summer	Winter	Winter	Winter
14		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
15	Red	50	55	60	65	70	75	67.5	60	52.5	45	37.5	30
16	Yellow	20	25	30	35	40	45	37.5	30	22.5	15	7.5	0
17	Green	30	35	40	45	50	55	47.5	40	32.5	25	17.5	10
18
19	Table 3: To calculate cost of products
20	Cost Amount	Winter	Winter	Winter	Summer	Summer	Summer	Summer	Summer	Summer	Winter	Winter	Winter
21		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
22	Product 1	32
23	Product 2
24	Product 3
25	Product 4
26	Product 5
27	Product 6
Sheet1

Cell Formulas
Range	Formula
B22	B22	=B5*B15+C5*B16+D5*B17

 

[icytuvi]

I have solved the problem with the formula below, but would appreciate any suggestions if the product not only consist of 03 colors such as Red, Yellow and Green but a dozen.

Thanks.

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
19	Table 3: To calculate cost of products
20	Cost Amount	Winter	Winter	Winter	Summer	Summer	Summer	Summer	Summer	Summer	Winter	Winter	Winter
21		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
22	Product 1	32	37	42	52	57	62	54.5	47	39.5	27	19.5	12
23	Product 2	33.25	38.25	43.25	53.25	58.25	63.25	55.75	48.25	40.75	28.25	20.75	13.25
24	Product 3	34.5	39.5	44.5	54.5	59.5	64.5	57	49.5	42	29.5	22	14.5
25	Product 4	35.75	40.75	45.75	55.75	60.75	65.75	58.25	50.75	43.25	30.75	23.25	15.75
26	Product 5	37	42	47	57	62	67	59.5	52	44.5	32	24.5	17
27	Product 6	38.25	43.25	48.25	58.25	63.25	68.25	60.75	53.25	45.75	33.25	25.75	18.25
Sheet1

Cell Formulas
Range	Formula
B22:M27	B22	=SUMPRODUCT(($A$15:$A$17="Red")*($B$14:$M$14=B$21)*($B$15:$M$17)*1)*SUMPRODUCT(($B$3:$G$3=B$20)*($B$4:$G$4="Red")*($A$5:$A$10=$A22)*($B$5:$G$10)*1)+SUMPRODUCT(($A$15:$A$17="Yellow")*($B$14:$M$14=B$21)*($B$15:$M$17)*1)*SUMPRODUCT(($B$3:$G$3=B$20)*($B$4:$G$4="Yellow")*($A$5:$A$10=$A22)*($B$5:$G$10)*1)+SUMPRODUCT(($A$15:$A$17="Green")*($B$14:$M$14=B$21)*($B$15:$M$17)*1)*SUMPRODUCT(($B$3:$G$3=B$20)*($B$4:$G$4="Green")*($A$5:$A$10=$A22)*($B$5:$G$10)*1)

 

[CA_Punit]

Consider this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Table 1: Color blending ratio of products
2	Blending %	Winter	Winter	Winter	Summer	Summer	Summer
3		Red	Yellow	Green	Red	Yellow	Green
4	Product 1	0.25	0.3	0.45	0.5	0.3	0.2
5	Product 2	0.3	0.275	0.425	0.55	0.275	0.175
6	Product 3	0.35	0.25	0.4	0.6	0.25	0.15
7	Product 4	0.4	0.225	0.375	0.65	0.225	0.125
8	Product 5	0.45	0.2	0.35	0.7	0.2	1.00E-01
9	Product 6	0.5	0.175	0.325	0.75	0.175	7.50E-02
10
11	Table 2: Cost of paint
12	Unit cost	Winter	Winter	Winter	Summer	Summer	Summer	Summer	Summer	Summer	Winter	Winter	Winter
13		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
14	Red	50	55	60	65	70	75	67.5	60	52.5	45	37.5	30
15	Yellow	20	25	30	35	40	45	37.5	30	22.5	15	7.5	0
16	Green	30	35	40	45	50	55	47.5	40	32.5	25	17.5	10
17
18	Table 3: To calculate cost of products
19	Cost Amount	Winter	Winter	Winter	Summer	Summer	Summer	Summer	Summer	Summer	Winter	Winter	Winter
20		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
21	Product 1	32	37	42	52	57	62	54.5	47	39.5	27	19.5	12
22	Product 2	33.25	38.25	43.25	53.25	58.25	63.25	55.75	48.25	40.75	28.25	20.75	13.25
23	Product 3	34.5	39.5	44.5	54.5	59.5	64.5	57	49.5	42	29.5	22	14.5
24	Product 4	35.75	40.75	45.75	55.75	60.75	65.75	58.25	50.75	43.25	30.75	23.25	15.75
25	Product 5	37	42	47	57	62	67	59.5	52	44.5	32	24.5	17
26	Product 6	38.25	43.25	48.25	58.25	63.25	68.25	60.75	53.25	45.75	33.25	25.75	18.25
Sheet1

Cell Formulas
Range	Formula
B21:M26	B21	=SUM(TRANSPOSE(IF($B$2:$G$2=B$19,INDEX($B$4:$G$9,MATCH($A21,$A$4:$A$9,0),0)))*INDEX(INDEX($B$14:$M$16,,MATCH(B$20,$B$13:$M$13,0)),{1;2;3;1;2;3}))

 

[CA_Punit]

if you have more colours (say if you have 4 colour just edit {1;2;3;1;2;3} to {1;2;3;4;1;2;3;4})

also the colour combination needs to be in sequence in both columns and rows i.e. RED, Yellow & Green. If it is changed in A14:16 then B3:D3 should also change in that order

 

[icytuvi]

if you have more colours (say if you have 4 colour just edit {1;2;3;1;2;3} to {1;2;3;4;1;2;3;4})

also the colour combination needs to be in sequence in both columns and rows i.e. RED, Yellow & Green. If it is changed in A14:16 then B3:D3 should also change in that order

Thanks a lot. This formula helps save me a lot of time typing :D

Still I am seeking a more flexible solution if possible, as at times I will not be able to arrange the information in sequence and will need to identify the matching info among tons of those.

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Table 1: Color blending ratio of products
2	Blending %	Winter	Winter	Winter	Summer	Summer	Summer
3		Red	Yellow	Green	Red	Yellow	Green
4	Product 1	0.25	0.3	0.45	0.5	0.3	0.2
5	Product 2	0.3	0.275	0.425	0.55	0.275	0.175
6	Product 3	0.35	0.25	0.4	0.6	0.25	0.15
7	Product 4	0.4	0.225	0.375	0.65	0.225	0.125
8	Product 5	0.45	0.2	0.35	0.7	0.2	0.10
9	Product 6	0.5	0.175	0.325	0.75	0.175	0.07
10
11	Table 2: Cost of paint
12	Unit cost	Winter	Winter	Winter	Summer	Summer	Summer	Summer	Summer	Summer	Winter	Winter	Winter
13		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
14	Red	50	55	60	65	70	75	67.5	60	52.5	45	37.5	30
15	Yellow	20	25	30	35	40	45	37.5	30	22.5	15	7.5	0
16	Green	30	35	40	45	50	55	47.5	40	32.5	25	17.5	10
17
18	Table 3: To calculate cost of products
19	Cost Amount	Winter	Winter	Winter	Summer	Summer	Summer	Summer	Summer	Summer	Winter	Winter	Winter
20		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
21	Product 1	32	37	42	52	57	62	54.5	47	39.5	27	19.5	12
22	Product 2	33.25	38.25	43.25	53.25	58.25	63.25	55.75	48.25	40.75	28.25	20.75	13.25
23	Product 3	34.5	39.5	44.5	54.5	59.5	64.5	57	49.5	42	29.5	22	14.5
24	Product 4	35.75	40.75	45.75	55.75	60.75	65.75	58.25	50.75	43.25	30.75	23.25	15.75
25	Product 5	37	42	47	57	62	67	59.5	52	44.5	32	24.5	17
26	Product 6	38.25	43.25	48.25	58.25	63.25	68.25	60.75	53.25	45.75	33.25	25.75	18.25
Sheet1

Cell Formulas
Range	Formula
B21:M26	B21	=SUM(INDEX(INDEX(IF(B$19=$B$2:$G$2,$B$4:$G$9),ROW($A$4:$A$9)-ROW($A$4)+1,MATCH(TRANSPOSE($A$14:$A$16),IF(B$19=$B$2:$G$2,$B$3:$G$3),0)),MATCH($A21,$A$4:$A$9,0))*TRANSPOSE(INDEX($B$14:$M$16,,MATCH(B$20,$B$13:$M$13,0))))