How to SUM my Index(Match) result

Status
Not open for further replies.

babettedv

New Member
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!

Thanks a lot!

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Saurabhj

Active Member
Hi,

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

Regards,
Saurabh

mart37

Well-known Member
I think you can beter use SUMPRODUCT.

babettedv

New Member
Product Costing TEST.xlsx
ABCEFGHIJ
1Mince GivMince GivMince GivMince GivMince Giv
2COSRaw Material CodeDescriptionCost/kgMFPC050.V1MFPC048MFPC057MFPC131MFPC060
3IngredientsRM008554M - EXCEL FIT HI-CEL 554M (METHYL CELLULOSE SOLUTION)10.95#VALUE!0.260.260.260.26
4IngredientsRM011.001SUPRO 500 - SOYA PROTEIN ISOLATE - SUPRO 5002.950.050.070.070.070.07
5IngredientsRM014R00003 - REFINED RAPESEED OIL1.2336960.100.150.150.150.15
6IngredientsRM018ORAFTI HP4.780.100.140.140.140.14
7IngredientsRM010.0014410 - TEXTURED SOYA PROTEIN CONCENTRATE - DUPONT3.381.051.581.581.581.58
8IngredientsRM010.003CONTEX 181.84-----
9IngredientsRM025.001TEXTA POIS 24/30 - PEA PROTEIN1.0909090.060.090.090.090.09
10IngredientsRM02610124 - COMPLEX 2170.130.200.200.200.20
11IngredientsRM009RICE PROTEIN 80%5.650.050.070.070.070.07
12IngredientsRM007FST 0007 - CARROT FIBRE FST 0007 KARO PRO 1-264.250.030.050.050.050.05
15IngredientsRM029101006 - ASCORBIC ACID E300 40-80MM10.840.020.030.030.030.03
16IngredientsRM030BEEF (JUICY) FLAVOURING12.010.330.490.490.490.49
17IngredientsRM016GROUND BLACK PEPPER - BLPE25N3.88-----
18IngredientsRM020.001RSSL VMM 1-12/17 VEG - 44-1415 VITAMIN PRE MIX250.010.020.020.020.02
19IngredientsRM0032463 - GISTEX X11 POWDER AGGL 2463 (YEAST EXTRACT)6.320.020.030.030.030.03
20IngredientsRM002188502 - MAXAVOR BEEF 14484 (YEAST EXTRACT)15.210.070.110.110.110.11
CoS FLAT
Cell Formulas
RangeFormula
F3F3=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:J20G3=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

New Member

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

Product Costing TEST.xlsx
ABCDGHIJKL
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
RangeFormula
C10:C11,C23:C28,C19:C20,C14:C16C10=VLOOKUP(\$A10,#REF!,3,FALSE)

Fluff

MrExcel MVP, Moderator
The formula should be
Excel Formula:
``=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

New Member

Thanks a lot, it works!!!

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

babettedv

New Member
Hi there,

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

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

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

Status
Not open for further replies.

Replies
1
Views
322
Replies
6
Views
56
Replies
3
Views
228
Replies
9
Views
312
Replies
1
Views
287

1,141,203
Messages
5,704,931
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back