turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 150K Members
- 2,239 Online
- 36.8K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Cost estimator for small scale catering

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
## Cost estimator for small scale catering

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-09-2017 11:52 PM

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.

Labels:

12 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 12:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 12:38 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 01:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 02:19 AM

SolutionDear 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

Best Response confirmed by Louisa Dira (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 03:17 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 06:26 AM

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))

=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))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 06:27 AM

Its a pretty long formula but does the job correctly

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 07:08 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 07:18 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-10-2017 07:27 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-19-2017 02:19 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2017 10:24 AM

Good work!!!...

Solved it but this is a time saver.

Just one line of code.

Solved it but this is a time saver.

Just one line of code.

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft