Forum Discussion
Cost estimator for small scale catering
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.
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
12 Replies
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!
- Good work!!!...
Solved it but this is a time saver.
Just one line of code.
- Absolutely, it does. I'm stoked! Thanks again. Regards, Louisa
- 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))- Its a pretty long formula but does the job correctly
- 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
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
- 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
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
- 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!
You can use SUMIF to check in detail the breakdown. Attached is the modified Excel Spreadsheet with formula.
