How to SUM my Index(Match) result

[babettedv]

Hi all,

I have build a flexible Index Match formula that finds the cost of certain ingredient (A3) within a certain product (F2), from the recipe sheet (referred to with Indirect so I can change the name of the sheet).

=IFERROR(INDEX(INDIRECT("'"&F$1&"'!$B$8:$Y$45"),MATCH($A3,INDIRECT("'"&F$1&"'!$A$8:$A$59"),0),MATCH(F$2,INDIRECT("'"&F$1&"'!$B$7:$Y$7"),0)),0)

However, the certain ingredient shows up several times in the recipe list so I am looking for a way to SUM the outcomes of my Index Match?

If I add the SUM() function before it doesnt work!

Please help

Thanks a lot!

 

[Saurabhj]

Hi,

Please share the data using XL2BB tool. Would help us to copy the data in our excel and check.

Regards,
Saurabh

 

[mart37]

I think you can beter use SUMPRODUCT.

 

[babettedv]

Product Costing TEST.xlsx
	A	B	C		E	F	G	H	I	J
1						Mince Giv	Mince Giv	Mince Giv	Mince Giv	Mince Giv
2	COS	Raw Material Code	Description		Cost/kg	MFPC050.V1	MFPC048	MFPC057	MFPC131	MFPC060
3	Ingredients	RM008	554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)		10.95	#VALUE!	0.26	0.26	0.26	0.26
4	Ingredients	RM011.001	SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500		2.95	0.05	0.07	0.07	0.07	0.07
5	Ingredients	RM014	R00003 - REFINED RAPESEED OIL		1.233696	0.10	0.15	0.15	0.15	0.15
6	Ingredients	RM018	ORAFTI HP		4.78	0.10	0.14	0.14	0.14	0.14
7	Ingredients	RM010.001	4410 - TEXTURED SOYA PROTEIN CONCENTRATE - DUPONT		3.38	1.05	1.58	1.58	1.58	1.58
8	Ingredients	RM010.003	CONTEX 18		1.84	-	-	-	-	-
9	Ingredients	RM025.001	TEXTA POIS 24/30 - PEA PROTEIN		1.090909	0.06	0.09	0.09	0.09	0.09
10	Ingredients	RM026	10124 - COMPLEX 21		7	0.13	0.20	0.20	0.20	0.20
11	Ingredients	RM009	RICE PROTEIN 80%		5.65	0.05	0.07	0.07	0.07	0.07
12	Ingredients	RM007	FST 0007 - CARROT FIBRE FST 0007 KARO PRO 1-26		4.25	0.03	0.05	0.05	0.05	0.05
13	Ingredients	RM021	CF013447A - BURGER SHADE RED		22	-	-	-	-	-
14	Ingredients	RM017	49420001 - EXBERRY SHADE BROWN		6.363636	0.07	0.11	0.11	0.11	0.11
15	Ingredients	RM029	101006 - ASCORBIC ACID E300 40-80MM		10.84	0.02	0.03	0.03	0.03	0.03
16	Ingredients	RM030	BEEF (JUICY) FLAVOURING		12.01	0.33	0.49	0.49	0.49	0.49
17	Ingredients	RM016	GROUND BLACK PEPPER - BLPE25N		3.88	-	-	-	-	-
18	Ingredients	RM020.001	RSSL VMM 1-12/17 VEG - 44-1415 VITAMIN PRE MIX		25	0.01	0.02	0.02	0.02	0.02
19	Ingredients	RM003	2463 - GISTEX X11 POWDER AGGL 2463 (YEAST EXTRACT)		6.32	0.02	0.03	0.03	0.03	0.03
20	Ingredients	RM002	188502 - MAXAVOR BEEF 14484 (YEAST EXTRACT)		15.21	0.07	0.11	0.11	0.11	0.11
CoS FLAT

Cell Formulas
Range	Formula
F3	F3	=SUMIFS(INDEX(INDIRECT("'"&F$1&"'!$C$8:$Y$100"),0,MATCH(F$2,INDIRECT("'"&F$1&"'!$C$7:$Y$7"),0)),INDIRECT("'"&F$1&"'!$C$8:$B$100"),$C3)
G3:J3,F4:J20	G3	=IFERROR(INDEX(INDIRECT("'"&G$1&"'!$B$8:$Y$100"),MATCH($C3,INDIRECT("'"&G$1&"'!$B$8:$B$100"),0),MATCH(G$2,INDIRECT("'"&G$1&"'!$B$7:$Y$7"),0)),0)

 

[babettedv]

CoS Flat is where I have the formula. The sheet it refers to:

Product Costing TEST.xlsx
	A	B	C	D			G	H	I	J	K	L
7	RM CODE	INGREDIENTS	SUPPLIER	%				x	x	MFPC050.V1	x	MFPC048
8
9		Methyl cellulose solution
10	RM008	554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)	#REF!	1.00%				£ 10.95	£ 0.11	£ 0.18	£ 0.26	£ 0.26
11	RM022	WATER	#REF!	30.00%				£ -	£ -	£ -	£ -	£ -
12
13		Soya Protein Emulsion
14	RM011.001	SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500	#REF!	1.00%				£ 2.95	£ 0.03	£ 0.05	£ 0.07	£ 0.07
15	RM014	R00003 - REFINED RAPESEED OIL	#REF!	5.50%				£ 1.16	£ 0.06	£ 0.10	£ 0.15	£ 0.15
16	RM022	WATER	#REF!	5.00%				£ -	£ -	£ -	£ -	£ -
17
18		Inulin gel
19	RM018	ORAFTI HP	#REF!	1.25%				£ 4.78	£ 0.06	£ 0.10	£ 0.14	£ 0.14
20	RM022	WATER	#REF!	3.75%				£ -	£ -	£ -	£ -	£ -
21
22		Other
23	RM010.001	4410 - TEXTURED SOYA PROTEIN CONCENTRATE - DUPONT	#REF!	19.50%				£ 3.38	£ 0.66	£ 1.05	£ 1.58	£ 1.58
24	RM025.001	TEXTA POIS 24/30 - PEA PROTEIN	#REF!	3.30%				£ 1.09	£ 0.04	£ 0.06	£ 0.09	£ 0.09
25	RM026	10124 - COMPLEX 21	#REF!	1.20%				£ 7.00	£ 0.08	£ 0.13	£ 0.20	£ 0.20
26	RM011.001	SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 500	#REF!	4.60%				£ 2.95	£ 0.14	£ 0.22	£ 0.33	£ 0.33
27	RM009	RICE PROTEIN 80%	#REF!	0.50%				£ 5.65	£ 0.03	£ 0.05	£ 0.07	£ 0.07
28	RM007	FST 0007 - CARROT FIBRE FST 0007 KARO PRO 1-26	#REF!	0.50%				£ 4.25	£ 0.02	£ 0.03	£ 0.05	£ 0.05
Mince Giv

Cell Formulas
Range	Formula
C10:C11,C23:C28,C19:C20,C14:C16	C10	=VLOOKUP($A10,#REF!,3,FALSE)

 

[Fluff]

The formula should be

=SUMIFS(INDEX(INDIRECT("'"&F$1&"'!$C$8:$Y$100"),0,MATCH(F$2,INDIRECT("'"&F$1&"'!$C$7:$Y$7"),0)),INDIRECT("'"&F$1&"'!$B$8:$B$100"),$C3)

You had the wrong range in the final Indirect function.
Also on you Mince Giv sheet you appear to have leading/trailing spaces which you will need to remove.

 

[babettedv]

Thanks a lot, it works!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[babettedv]

Hi there,

Thanks again for you help on the formula above, it works well.

As an additional question,
Is it possible to look for F2 (bold) in a range over multiple columns AND multiple rows --> C7:Y12 (instead of C7:Y7)

=SUMIFS(INDEX(INDIRECT("'"&F$1&"'!$C$8:$Y$100"),0,MATCH(F$2,INDIRECT("'"&F$1&"'!$C$7:$Y$7"),0)),INDIRECT("'"&F$1&"'!$B$8:$B$100"),$C3)


Looking forward to hear from you, thanks!

 

[Fluff]

As you have started a new thread for this question, you will need to stick to it.