VLookup or Sumproduct or combined? This is getting out of hand

[CampIntense]

Hi!

This is my first post, but i have regularly used this forum to figure out my issue, however this one i cannot get and I am hoping for some help.

I have a sheet that has items on it, that contain ingredients. The ingredients can be in multiple instances.

On the other sheet i have a schedule of what is to be produced and the qty and this counts the qty required from the first sheet.

There are 6 different productions so i have listed all indvidually and then done a SUMPRODUCT for each one and added it all together, however this makes it unwieldy. Currently i use =SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$E$1)*(Master!$H$2:$H$2210=Schedule!$A14)*(Master!$L$2:$L$2210)*E$2) and then add the same formula 6 times (for each item being produced) to come up with a total, surely there is a better way?

I can provide a sample of some data if that helps to understand what I am on about?

Thanks Grant

[6StringJazzer]

Are you saying that

You have a list of items
Each item has a list of ingredients, with the quantity needed of each ingredient
A given ingredient can appear in multiple items
You want to have another sheet that totals up how much of each ingredient you need to make all of the items

A sample of data would certainly help, especially since we can't tell what your example formula does without knowing what's in column A, H, L, etc.

[CampIntense]

Thanks for the response, i have made an example spreadsheet which i will attach, hopefully it should be pretty straight forward, i would like to change the SUMPRODUCT formulas on sheet 1 (schedule) to something more concise as there are going to be 1000's of items looking up this formula.

[kvsrinivasamurthy]

Are the results of formula is Ok.
For all the formulas in D to M columns the "V" values are refered to the column D only. I feel the "V" valued are to be refered to resective column.
For Eg in F14 formula should be

=SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$F$1)*(Master!$E$2:$E$2210=Schedule!$A$14)*(Master!$H$2:$H$2210)*F$2)+SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$F$3)*(Master!$E$2:$E$2210=Schedule!$A$14)*(Master!$H$2:$H$2210)*F$4)+SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$F$5)*(Master!$E$2:$E$2210=Schedule!$A$14)*(Master!$H$2:$H$2210)*F$6)+SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$F$7)*(Master!$E$2:$E$2210=Schedule!$A$14)*(Master!$H$2:$H$2210)*F$8)+SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$F$9)*(Master!$E$2:$E$2210=Schedule!$A$14)*(Master!$H$2:$H$2210)*F$10)+SUMPRODUCT((Master!$A$2:$A$2210=Schedule!$F$11)*(Master!$E$2:$E$2210=Schedule!$A$14)*(Master!$H$2:$H$2210)*F$12)

Result is 16.10

[CampIntense]

Ahh yes that is right, the formula in F14 should be attributed to the F column. I must of accidentally changed the formula when i made the example, in my full spreadsheet the formula works however i just want to make it more concise as there will be over 1000 items populating the full spreadsheet.

[kvsrinivasamurthy]

Pl see the file.
I have changed the format of data in A1:M12 to O1:Y12
Formula in P12 then copied to other required ranges.

=SUMPRODUCT(SUMIFS(Master!$H$2:$H$42,Master!$A$2:$A$42,Schedule!P$1:P$6,Master!$E$2:$E$42,$A14)*P$7:P$12)

[CampIntense]

Yes! Thats much better thanks! Why didnt i think of changing those ranges to sequences instead of alternating them.. Thanks again!