Sum multiple lookups, based on multiple criteria

[Eat More Bacon]

Can I search column A of ingredients and check column b to make sure quantity is higher than 0, then lookup column a and sum all the returned lookup values?

sheet 1
column a, column b
corn, 100
wheat, 40

Then lookup ingredients in column a and return this week's price from sheet 2 and sum all returned prices

Sheet2
Ingredient, Last week price, This week price, next week price
corn, 6.80, 7.00, 7.10
wheat, 6.5, 6.8, 7

So I want to sum all ingredient prices if column b is greater than 0

 

[pecoflyer]

Please post a sample sheet with some data and expected results. Thx

 

[Eat More Bacon]

I think I can get it with a hidden column. I was just thinking that surely there would be a way to do it without adding another hidden column.

For any ingredients that start with med in column A, I want to lookup a value on the Cost Comparison sheet, then I want to sum all those values (& 2 more) in g52.

I thought maybe sumproduct would work, but for some reason I just couldn't get it to give the correct result. Must be I didn't have the right syntax.

See attached file. Column D will be hidden.

 

[pecoflyer]

Perhaps first reshape your sheet removing ALL merged cells. They are impossible to work with. Thx

Using Merged Cells Effectively in Excel - Excel Solutions

There's one simple piece of advice for using merged cells effectively in Excel...

excel.solutions

 

[Eat More Bacon]

Good advice. How do you deal with needing one row some places and two rows some places in same sheet?

I deleted most of the sheet to save space, but just to the right of what I sent, I have two rows. one for weight, one for price

 

[bosco_yip]

I think I can get it with a hidden column. I was just thinking that surely there would be a way to do it without adding another hidden column.

For any ingredients that start with med in column A, I want to lookup a value on the Cost Comparison sheet, then I want to sum all those values (& 2 more) in g52.

I thought maybe sumproduct would work, but for some reason I just couldn't get it to give the correct result. Must be I didn't have the right syntax.

See attached file. Column D will be hidden.

This is your original posted formula in G52 and with the helper column in Column D

=SUM(D6:D48)+VLOOKUP(F52,CostCompList,MATCH(H1,CostCompDate,0),0)+'Cost Comparison'!$C$24

And,

Try to use this formula in G52 instead without the helper :

=SUMPRODUCT(SUMIF('Cost Comparison'!A2:A50,A4:A48,INDEX(CostCompList,0,MATCH(H$1,CostCompDate,0))))+VLOOKUP(F52,CostCompList,MATCH(H1,CostCompDate,0),0)+'Cost Comparison'!$C$24

Remark : red color portion is the replacement

 

[Eat More Bacon]

Thanks!