How to do a sum for multiple tables

[Jessica553]

Hello, I am trying to figure out a formula to work out the cost of ingredients in different stores and then also look at the cost for the whole recipe at the same time. I have an example with really basic amounts in it just to give an idea.
I have built it with a really basic =(A1 * I1) + (A2 * I2) etc. But when it gets more complicated ie more ingredients, recipes etc and I need to update the prices in the stores or add in other ingredients like sprinkles etc I don't want to have to run that formula everytime for 10+ ingredients. Is there an easier way to set this up? I have looked at INDEX MATCH but don't know how to build that in with a sum etc. Thanks in advance for any help. Sorry I can't get the XL2BB to work.

 

[awoohaw]

You can try this, but this requires your sumproduct table to have all ingredients in the same order.(I hope you understand that).
(You may need to enter the formula with the CSE entry (CNTL-SHFT-ENTR) method.)

Mr excel questions 60.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3			Flour	Butter	Milk	Eggs			Flour	Butter	Milk	Eggs
4		Woolworth's	1	1	0.5	1.5		Cake	5	2	1	2
5		Coles	1.5	1	0.49	1.49		Brownie	4	1	1	1
6								Slice	3	2	0	1
7		Cake	Brownie
8	Woolworth's	10.5	7
9	Coles	12.97	8.98
10
11
Jessica553

Cell Formulas
Range	Formula
B8:C9	B8	=SUMPRODUCT(INDEX($C$4:$F$5,MATCH($A8,$B$4:$B$5,0),0)*INDEX($I$4:$L$6,MATCH(B$7,$H$4:$H$6,0),0))

 

[Scott R]

in cell B8: =SUM( FILTER( $C$4:$F$5, $B$4:$B$5 = $B8) * FILTER( $I$4:$L$6, $H$4:$H$6 = C$7 ))

 

[Fluff]

@Scott R The OP's profile shows 2010 & therefore does not have the Filter function.

 

[Jessica553]

You can try this, but this requires your sumproduct table to have all ingredients in the same order.(I hope you understand that).
(You may need to enter the formula with the CSE entry (CNTL-SHFT-ENTR) method.)

Mr excel questions 60.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3			Flour	Butter	Milk	Eggs			Flour	Butter	Milk	Eggs
4		Woolworth's	1	1	0.5	1.5		Cake	5	2	1	2
5		Coles	1.5	1	0.49	1.49		Brownie	4	1	1	1
6								Slice	3	2	0	1
7		Cake	Brownie
8	Woolworth's	10.5	7
9	Coles	12.97	8.98
10
11
Jessica553

Cell Formulas
Range	Formula
B8:C9	B8	=SUMPRODUCT(INDEX($C$4:$F$5,MATCH($A8,$B$4:$B$5,0),0)*INDEX($I$4:$L$6,MATCH(B$7,$H$4:$H$6,0),0))

You're amazing, that worked perfectly thank you so much!

 

[awoohaw]

You're welcome. I am happy you found a solution!

Best Wishes!