• 173K Members
• 3,999 Online
• 42.9K Conversations
SOLVED

## Cost estimator for small scale catering

Highlighted
Occasional Contributor

## 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.

12 Replies

## Re: Cost estimator for small scale catering

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

## Re: Cost estimator for small scale catering

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!

## Re: Cost estimator for small scale catering

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,

Panaji, Goa, India

Solution

## Re: Cost estimator for small scale catering

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,

Panaji, Goa, India

## Re: Cost estimator for small scale catering

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

## Re: Cost estimator for small scale catering

you can also do this:

## Re: Cost estimator for small scale catering

Its a pretty long formula but does the job correctly

## Re: Cost estimator for small scale catering

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

## Re: Cost estimator for small scale catering

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

## RE: Cost estimator for small scale catering

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

## Re: Cost estimator for small scale catering

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!

## Re: Cost estimator for small scale catering

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