Dynamic Sumproduct

[Hrishi]

Hello, following is the sample data for quick reference.
there are 4 towers, 5 types of material and 4 suppliers.
i need a formula linked to input cell of tower and vendor, wherein for any combination of tower and vendor , result should show sumproduct for all the list of material.

		QTY	QTY	QTY	QTY	QTY	VENDOR RATES	VENDOR RATES	VENDOR RATES	VENDOR RATES
SR NO	ITEM	TOWER1	TOWER2	TOWER3	TOWER4	TOTAL QTY	ABC	DEF	GHI	JKL
1	sand	10.00	32.00	22.00	42.00	106.00	50.00	55.00	65.00	45.00
2	cement	23.00	41.00	34.00	34.00	132.00	300.00	270.00	300.00	290.00
3	steel	35.00	23.00	52.00	22.00	132.00	60.00	55.00	66.00	58.00
4	metal 1	25.00	16.00	55.00	61.00	157.00	40.00	38.00	42.00	37.00
5	metal 2	16.00	22.00	12.00	26.00	76.00	40.00	38.00	40.00	39.00
	TOTAL	109.00	134.00	175.00	185.00	603.00

RESULT
SR NO	TOWER	VENDOR	AMOUNT
1	TOWER1	ABC	11,140.00
TOTAL			11,140.00

i have made dropdown list for tower and vendor, but unable to make formula dynamic.
pls help.

 

[James006]

Hi,

Have you tried to insert a Pivot Table ?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1			QTY	QTY	QTY	QTY	QTY	VENDOR RATES	VENDOR RATES	VENDOR RATES	VENDOR RATES
2	SR NO	ITEM	TOWER1	TOWER2	TOWER3	TOWER4	TOTAL QTY	ABC	DEF	GHI	JKL
3	1	sand	10	32	22	42	106	50	55	65	45
4	2	cement	23	41	34	34	132	300	270	300	290
5	3	steel	35	23	52	22	132	60	55	66	58
6	4	metal 1	25	16	55	61	157	40	38	42	37
7	5	metal 2	16	22	12	26	76	40	38	40	39
8		TOTAL	109	134	175	185	603
9
10
11		Tower1	abc			11140
12
Main

Cell Formulas
Range	Formula
F11	F11	=SUMPRODUCT(INDEX(C3:F7,,MATCH(B11,C2:F2,0)),INDEX(H3:K7,,MATCH(C11,H2:K2,0)))

 

[Hrishi]

Hi,

Have you tried to insert a Pivot Table ?

Pivot will not solve the purpose, as this sample data simple, my real data is complex and not in a particular order.

 

[Hrishi]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1			QTY	QTY	QTY	QTY	QTY	VENDOR RATES	VENDOR RATES	VENDOR RATES	VENDOR RATES
2	SR NO	ITEM	TOWER1	TOWER2	TOWER3	TOWER4	TOTAL QTY	ABC	DEF	GHI	JKL
3	1	sand	10	32	22	42	106	50	55	65	45
4	2	cement	23	41	34	34	132	300	270	300	290
5	3	steel	35	23	52	22	132	60	55	66	58
6	4	metal 1	25	16	55	61	157	40	38	42	37
7	5	metal 2	16	22	12	26	76	40	38	40	39
8		TOTAL	109	134	175	185	603
9
10
11		Tower1	abc			11140
12
Main

Cell Formulas
Range	Formula
F11	F11	=SUMPRODUCT(INDEX(C3:F7,,MATCH(B11,C2:F2,0)),INDEX(H3:K7,,MATCH(C11,H2:K2,0)))

PERFECT. THANKS

 

[Fluff]

You're welcome & thanks for the feedback.

Please also update you profile as requested.