Cost estimator for small scale catering

[Louisa Dira]

Hello, I need a formula to calculate how much it would cost all up to make a menu item eg, chicken sandwich by looking up each code and multiplying the quantity with its associated unit cost. I have attached the excel spreadsheet which has two sheets MENU & ITEMS. Any help is much appreciated, thanks. 

[#2]

You can use SUMIF to check in detail the breakdown. Attached is the modified Excel Spreadsheet with formula.

[#3]

Thankyou very much for this! I will work with this and add on to my growing list. It is abit tedious but copy/paste down should help. Thanks again!

[#4]

Dear Lousa Dira,

Enter the following Array formula in cell C2 and drag it down.

Array formulas are to be Entered using Control+Shift+Enter instead of Enter.

=SUM(IF(ISERROR(SEARCH("Qty",$E$1:$AG$1)),0,1)*IFERROR(VLOOKUP(D2:AF2,ITEMS!$C$2:$E$48,3,FALSE),0)*IF(ISNUMBER(E2:AG2),E2:AG2),0)

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

[#5 · Accepted solution]

Dear Lousa Dira,

I need to make some ammendments in the previous array formula.

In the mean time, you may use the attached Excel file which contains 2 independent methods of doing the activity. Choose the one you find most appropriate.

Do let me know if any clarification is required.

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

[#6]

Hi Vijaykumar,
Great! Yes this is very helpful; the added column for cost helps although I wanted to keep it separate in another sheet. I will work through and see which works best for me. Thanks!
Regards,
Louisa

[#7]

you can also do this:
=SUM(IF(D5>0,(VLOOKUP(D5,ITEMS!$C$2:$E$48,3,FALSE)*E5),0),IF(F5>0,(VLOOKUP(F5,ITEMS!$C$2:$E$48,3,FALSE)*G5),0),IF(H5>0,(VLOOKUP(H5,ITEMS!$C$2:$E$48,3,FALSE)*I5),0),IF(J5>0,(VLOOKUP(J5,ITEMS!$C$2:$E$48,3,FALSE)*K5),0),IF(L5>0,(VLOOKUP(L5,ITEMS!$C$2:$E$48,3,FALSE)*M5),0),IF(N5>0,(VLOOKUP(N5,ITEMS!$C$2:$E$48,3,FALSE)*O5),0),IF(P5>0,(VLOOKUP(P5,ITEMS!$C$2:$E$48,3,FALSE)*Q5),0),IF(R5>0,(VLOOKUP(R5,ITEMS!$C$2:$E$48,3,FALSE)*S5),0),IF(T5>0,(VLOOKUP(T5,ITEMS!$C$2:$E$48,3,FALSE)*U5),0),IF(V5>0,(VLOOKUP(V5,ITEMS!$C$2:$E$48,3,FALSE)*W5),0),IF(X5>0,(VLOOKUP(X5,ITEMS!$C$2:$E$48,3,FALSE)*Y5),0),IF(Z5>0,(VLOOKUP(Z5,ITEMS!$C$2:$E$48,3,FALSE)*AA5),0),IF(AB5>0,(VLOOKUP(AB5,ITEMS!$C$2:$E$48,3,FALSE)*AC5),0),IF(AD5>0,(VLOOKUP(AD5,ITEMS!$C$2:$E$48,3,FALSE)*AE5),0),IF(AF5>0,(VLOOKUP(AF5,ITEMS!$C$2:$E$48,3,FALSE)*AG5),0))

[#8]

Its a pretty long formula but does the job correctly

[#9]

Hi Zachary,
Thanks so much! I copied the formula and even though it is long, I can follow and it does the trick! All I have to do is update the range whenever I add more items on the list. Thankyou for your time in writing up the formula.
Regards,
Louisa

[#10]

You're welcome, hope this saves you lots of time!

[#11]

Absolutely, it does. I'm stoked! Thanks again. Regards, Louisa

[#12]

Hi to all!

Without helper cells, you can use this formulae (in C2 and drag it down):

=SUMPRODUCT(SUMIF(CODE,D2:AF2,ITEMS!E$2:E$48),E2:AG2)

See attached.  Blessings!

[#13]

Good work!!!...
Solved it but this is a time saver.
Just one line of code.