Creating an Excel Formula to Calculate Total Cost for a List of Items in Square Brackets Separated by Commas

Question

I have a list of items with their corresponding prices stored in a separate sheet named 'Menu Costs' in Excel. I want to create a formula in another sheet that calculates the total cost for a list of items I enter into a cell. Each item will be enclosed within square brackets, and I will separate the items with commas if there is more than one item.

My Objective:

1. Extract the individual items from the input cell.
2. Look up the prices for each item in the 'Menu Costs' sheet.
3. Multiply each item's price by a specified quantity.
4. Sum up the individual costs to calculate the total cost.

like this ["Hot/Cold Drinks & biscuit","Tray bake/cookie"]

Excel sheet:

Menu Costs:

Current formula in cost column which works if I use one Food field within the Food Options column

=VLOOKUP(E2,'Menu Costs'!A:B,2,FALSE)*C2

PLease help!

Answer 1

I don't think there is a single formula that will do what you want but a pair of macro functions can do the job easily.

The first macro function (e.g., Quantity_Cost) is a subroutine that accepts a food name as a string and a quantity as an integer. It looks up the item price in for that name in Menu Costs, multiplies that value by the quantity, and returns the product to the calling macro.

The second macro function (e.g., Total_Cost) is the main routine. It extracts the text in the corresponding cell in the Food Option column. It determines if this text represents a single Food (as in rows 1, 2, and 3 of your sample) or it represents several Foods, each enclosed in quotes, and the entire list enclosed in brackets (as in row 5)

• For a single Food, call Quantity_Cost passing the text and the quantity (not shown in your sample). Return the value from this call to the cell from which Total_Cost was called (the Cost column in your sample).
• For multiple foods, you need a loop.
  • Find the first quote and note its location. If none, exit the loop.
    • Find the next quote and note its location. If none, the data is malformed.
      • Extract the text between, but not including, the quotes.
        • Call Quantity_Cost passing the extracted text and the quantity.
          • Add the value returned from this call to an accumulator.
            • Repeat the loop
• Return the value in the accumulator to the cell from which Total_Cost was called.

You can add additional error checking as you wish. For example: check for both brackets; check for a comma between quoted foods; verify the Food exists in Menu Costs; etc.