Sum the cost of the selected ingredients for each burger, to give total cost of burger.

Hi,

I am re-making this thread because the earlier one ("Is this component included? Ok then add to total cost." - http://www.excelforum.com/excel-form...otal-cost.html) turned out to be too vague.

This time, l have also attached an example spreadsheet for illustration - please see attachment.

As the title of this new thread indicates: I have several burgers on a menu. Each burger uses a different set of ingredients. Each ingredient has a specific cost, listed in the "Ingredient costs" subsection of the spreadsheet.

For each burger: Wherever an ingredient is specified, l want its cost to be cross-referenced from the "Ingredient costs" subsection, and added to the cost of all other ingredients for that burger, and l want the sum placed in the "Total Cost of this burger" column.

Caveat: Whenever Ingredient D has been selected with a "Y", the cost of Ingredient D is only added to Column J if Column F ("Is D available?") also has a "Y"

try this one, i transpozed ingredient list to make sumproduct work, and changed how ingredient d works

Hi bolulusam, your solution works except for the caveat But thank you for what you did.

Originally Posted by hemesh

One approach could be copy paste below in J2 then hold control and shift together and hit enter to make array formula
=SUM(IF(MID(IF($B2:$D2="y",$B$1:$D$1,0),12,1)=$L$2:$L$8,$M$2:$M$8))+SUM(IF(MID(IF($G2:$I2="y",$G$1:$I$1,0),12,1)=$L$2:$L$8,$M$2:$M$8))+SUM(IF(AND(E2="y",F2="y"),$M$5))

Hi hemesh, your solution works, thanks! Rep point on its way

Hi MarvinP, your solution doesn't work, the values it gives are too high, and it ignores the caveat. Sorry

HI Mazzaropi, your solution is the most elegant, and it doesn't even need an array. Thank you very much, l have adapted and used it (l don't need the Vlookup actually, l refer directly to value cells, instead of referring to the titles of those cells). Rep point on its way!