Sumproduct with 3 tables?

[killerstein]

Dear users,

I am trying to do the following but have been unsuccessful therefore I seek your help;

I have vendors who buy TVs from certain Towns. The top left table displays the quantities of TVs bought by each vendor who has a record of purchase while the bottom left table displays the sourcing structure of each vendor (the proportion of TVs purchased from each town). With these data, I would like to fill the top right table, which should show how many TVs a specific town has supplied in a specific month. Thus, for a specific cell on the top right table, I would like to lookup/match the name of the town in the bottom left table, I would like to lookup the name of all the vendors with records (top left table) in the bottom left table and then multiply the respective quantities with the respective proportions and finally sum them all to find how many TVs a town supplied to all the vendors in total for a specific month.

For example, the quantities for town F (in all the months) should be vendor 5's quantities multiplied by 0.5 because vendor 5 is the only one with qty data that sources from town F and its sourcing ratio is 0.5 (50%). On the other hand, for town M it should be 0.5*(qty of vendor 2) + 0.04*(qty of vendor 5).

I am unsure what formula I should use; I tried sumproduct with lookup/match, or sumifs, but could not figure out a way.

 

[Eric W]

First of all, I'd recommend you investigate the xl2bb tool. (See the link in my signature, or in the reply box.) This allows you to put a mini-sheet in your message, that anyone can copy and paste into their Excel to work on it. Most people will not take the time to manually retype your entire sheet from a picture. You're likely to get quicker and better solutions if you do this.

Next, consider this:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
5		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6	Vendor 1	141984	163910												Town A	0	0
7	Vendor 2	3593	4580												Town B	22804.87	8309.4
8	Vendor 3	3663	6643												Town C	10558.08	3078.4
9	Vendor 4														Town D	83978.23	85278.57
10	Vendor 5	23565	19987												Town E	0	0
11	Vendor 6	56357	69114												Town F	11782.5	9993.5
12	Vendor 7	40608	11840												Town G	100262.6	110850.1
13	Vendor 8	35776	22120												Town H	0	0
14	Vendor 9														Town I	0	0
15	Vendor 10														Town J	0	0
16	Vendor 11														Town K	0	0
17	Vendor 12														Town L	0	0
18	Vendor 13														Town M	2739.1	3089.48
19	Vendor 14
20	Vendor 15
21
22		Town A	Town B	Town C	Town D	Town E	Town F	Town G	Town H	Town I	Town J	Town K	Town L	Town M
23	Vendor 1				0.41			0.59
24	Vendor 2		0.47											0.5
25	Vendor 3				0.49			0.51
26	Vendor 4							0.5
27	Vendor 5						0.5							0.04
28	Vendor 6							0.05
29	Vendor 7		0.52	0.26
30	Vendor 8				0.67			0.33
31	Vendor 9		0.71	0.02
32	Vendor 10			0.75			0.05				0.2
33	Vendor 11	0.03	0.08	0.19
34	Vendor 12		0.81	0.19
35	Vendor 13				0.14			0.86
36	Vendor 14		0.87	0.04				0.04	0.03			0.02
37	Vendor 15												1
Sheet7

Cell Formulas
Range	Formula
Y6:Z18	Y6	=SUMPRODUCT(K$6:K$20*INDEX($K$23:$W$37,0,MATCH($X6,$K$22:$W$22,0)))

I made both of your source tables have the same number of vendors. This makes the solution much easier. Let us know how this works for you.

 

[killerstein]

First of all, I'd recommend you investigate the xl2bb tool. (See the link in my signature, or in the reply box.) This allows you to put a mini-sheet in your message, that anyone can copy and paste into their Excel to work on it. Most people will not take the time to manually retype your entire sheet from a picture. You're likely to get quicker and better solutions if you do this.

Next, consider this:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
5		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6	Vendor 1	141984	163910												Town A	0	0
7	Vendor 2	3593	4580												Town B	22804.87	8309.4
8	Vendor 3	3663	6643												Town C	10558.08	3078.4
9	Vendor 4														Town D	83978.23	85278.57
10	Vendor 5	23565	19987												Town E	0	0
11	Vendor 6	56357	69114												Town F	11782.5	9993.5
12	Vendor 7	40608	11840												Town G	100262.6	110850.1
13	Vendor 8	35776	22120												Town H	0	0
14	Vendor 9														Town I	0	0
15	Vendor 10														Town J	0	0
16	Vendor 11														Town K	0	0
17	Vendor 12														Town L	0	0
18	Vendor 13														Town M	2739.1	3089.48
19	Vendor 14
20	Vendor 15
21
22		Town A	Town B	Town C	Town D	Town E	Town F	Town G	Town H	Town I	Town J	Town K	Town L	Town M
23	Vendor 1				0.41			0.59
24	Vendor 2		0.47											0.5
25	Vendor 3				0.49			0.51
26	Vendor 4							0.5
27	Vendor 5						0.5							0.04
28	Vendor 6							0.05
29	Vendor 7		0.52	0.26
30	Vendor 8				0.67			0.33
31	Vendor 9		0.71	0.02
32	Vendor 10			0.75			0.05				0.2
33	Vendor 11	0.03	0.08	0.19
34	Vendor 12		0.81	0.19
35	Vendor 13				0.14			0.86
36	Vendor 14		0.87	0.04				0.04	0.03			0.02
37	Vendor 15												1
Sheet7

Cell Formulas
Range	Formula
Y6:Z18	Y6	=SUMPRODUCT(K$6:K$20*INDEX($K$23:$W$37,0,MATCH($X6,$K$22:$W$22,0)))

I made both of your source tables have the same number of vendors. This makes the solution much easier. Let us know how this works for you.

Hello Eric,

Thanks for your recommendation and apologies for the inconvenience as I was not aware of this (my 2nd post to date). The important detail is that the tables are not the same size (with regards to vendors), so I would really appreciate if the solution is more catered towards the situation I presented above, because indeed with the same number of vendors it is easier to do but the main difficulty was that also the numbers of columns/rows are different. Hopefully this is possible. Thanks in advance!

Kind Regards,
E.G.

 

[Eric W]

I still believe it makes more sense to design your tables in such a way to make reading and calculating with them easy, but here are some options:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
4
5		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6	Vendor 1	141984	163910												Town A	0	0
7	Vendor 2	3593	4580												Town B	22804.87	8309.4
8	Vendor 3	3663	6643												Town C	10558.08	3078.4
9	Vendor 4														Town D	83978.23	85278.57
10	Vendor 5	23565	19987												Town E	0	0
11	Vendor 6	56357	69114												Town F	11782.5	9993.5
12	Vendor 7	40608	11840												Town G	100262.62	110850.13
13	Vendor 8	35776	22120												Town H	0	0
14															Town I	0	0
15															Town J	0	0
16															Town K	0	0
17															Town L	0	0
18															Town M	2739.1	3089.48
19
20
21		Town A	Town B	Town C	Town D	Town E	Town F	Town G	Town H	Town I	Town J	Town K	Town L	Town M
22	Vendor 1				0.41			0.59
23	Vendor 2		0.47											0.5
24	Vendor 3				0.49			0.51
25	Vendor 4							0.5
26	Vendor 5						0.5							0.04
27	Vendor 6							0.05
28	Vendor 7		0.52	0.26
29	Vendor 8				0.67			0.33
30	Vendor 9		0.71	0.02
31	Vendor 10			0.75			0.05				0.2
32	Vendor 11	0.03	0.08	0.19
33	Vendor 12		0.81	0.19
34	Vendor 13				0.14			0.86
35	Vendor 14		0.87	0.04				0.04	0.03			0.02
36	Vendor 15												1
37
Sheet7

Cell Formulas
Range	Formula
Y6:Y18	Y6	=SUMPRODUCT(K$6:K$13*INDEX($K$22:$W$36,MATCH($J$6:$J$13,$J$22:$J$36,0),MATCH($X6,$K$21:$W$21,0)))
Z6:Z18	Z6	=SUMPRODUCT(L$6:L$13*SUMIFS(INDEX($K$22:$W$36,0,MATCH($X6,$K$21:$W$21,0)),$J$22:$J$36,$J$6:$J$13))

The first option (column Y), I don't believe will work in your version of Excel. The second probably will, but I'm not entirely sure.

 

[killerstein]

I still believe it makes more sense to design your tables in such a way to make reading and calculating with them easy, but here are some options:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
4
5		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6	Vendor 1	141984	163910												Town A	0	0
7	Vendor 2	3593	4580												Town B	22804.87	8309.4
8	Vendor 3	3663	6643												Town C	10558.08	3078.4
9	Vendor 4														Town D	83978.23	85278.57
10	Vendor 5	23565	19987												Town E	0	0
11	Vendor 6	56357	69114												Town F	11782.5	9993.5
12	Vendor 7	40608	11840												Town G	100262.62	110850.13
13	Vendor 8	35776	22120												Town H	0	0
14															Town I	0	0
15															Town J	0	0
16															Town K	0	0
17															Town L	0	0
18															Town M	2739.1	3089.48
19
20
21		Town A	Town B	Town C	Town D	Town E	Town F	Town G	Town H	Town I	Town J	Town K	Town L	Town M
22	Vendor 1				0.41			0.59
23	Vendor 2		0.47											0.5
24	Vendor 3				0.49			0.51
25	Vendor 4							0.5
26	Vendor 5						0.5							0.04
27	Vendor 6							0.05
28	Vendor 7		0.52	0.26
29	Vendor 8				0.67			0.33
30	Vendor 9		0.71	0.02
31	Vendor 10			0.75			0.05				0.2
32	Vendor 11	0.03	0.08	0.19
33	Vendor 12		0.81	0.19
34	Vendor 13				0.14			0.86
35	Vendor 14		0.87	0.04				0.04	0.03			0.02
36	Vendor 15												1
37
Sheet7

Cell Formulas
Range	Formula
Y6:Y18	Y6	=SUMPRODUCT(K$6:K$13*INDEX($K$22:$W$36,MATCH($J$6:$J$13,$J$22:$J$36,0),MATCH($X6,$K$21:$W$21,0)))
Z6:Z18	Z6	=SUMPRODUCT(L$6:L$13*SUMIFS(INDEX($K$22:$W$36,0,MATCH($X6,$K$21:$W$21,0)),$J$22:$J$36,$J$6:$J$13))

The first option (column Y), I don't believe will work in your version of Excel. The second probably will, but I'm not entirely sure.

Dear Eric,

Thank you very much. Both of the solutions have worked just fine. I also understand your point as to why I should arrange the tables in a more convenient way, however the tables in the screenshot/question were exemplary. In the real situation I have a similar case but in a pivot table format for the top left table, so that one cannot be arranged (vendors without data do not show up, as in your initial response which included vendors 9-15 for the top left table). The other ones I could arrange to my liking but I believed this was the best way to summarise for myself. Nonetheless, thanks for the efforts and time and I will take up to your advise for any of my future posts about the xl2bb.

Kind regards,
EG