0

Im building a cookbook for a restaurant that I work at in Numbers and need to total the amount of supplies I need for an ordering guide.

The ordering guide is as follows: Cooking Par Guide Example

I want the green cell to be the total amount of that ingredient. Each sheet in the book is a different recipe, like this:

Example of Recipe Sheet

I just want the par guide to grab the "Scaled Amount" for each ingredient and add them together, so I know how much to buy. Im hoping for it to do this automatically, so Im not manually looking for each cell to add to a total, instead using something like VLOOKUP to combine totals.

3
  • You seem to have provided quite a few clues as to what you need but your question seems to be still a bit too general as far as the working of Numbers is concerned. Can you tell us a bit more about what have you tried so far towards this specific problem (and did not work)?
    – Alper
    Aug 9 at 17:12
  • I tried stacking a VLOOKUP to search each sheet for the item in my prep list, but it would error out, I don't think I can add up values found by VLOOKUP, and I'm unsure how to quickly scan the whole book for each item Im looking for without manually going through and selecting it. Aug 10 at 10:09
  • I found in cases like this that using index along with match is much better than vlookup. Might get some ideas here. apple.stackexchange.com/a/199176/120171 along with sumif and good naming. Cheers
    – bjbk
    Aug 11 at 1:20
0

You can add up values found by two or more VLOOKUPs in the same formula assuming they are all numbers and there are no errors. However, for search functions such as VLOOKUP to work as intended, you need to have the same names to be used in both search formulas and the sheets being searched through except the capitalizations.

This implies a significant problem in your existing setup which is the respective names for most ingredients in the recipe sheets not being the same as those in the ordering guide sheet. For example, Maldon salt is called “Salt, Maldon” in the ordering guide sheet but “Maldon Salt” in the Pizza Dough recipe sheet. While both of these names might appear as nearly the same to a human, they are quite different to Numbers.

To fix this, for example, you should use the same name for Maldon salt everywhere including the ordering guide sheet. If you need to have a different name in the ordering guide sheet than in the recipe sheets, then you may add a (hidden) column before the “Description” columns in the ordering guide sheet, have the cells in these columns carry the same ingredient names as in the recipe sheets and use those to search for Maldon salt in the recipe sheets.

The other potential problem for you could be a too high number of recipes which could lead to too long search formulas. You need as many VLOOKUPs as the number of recipe sheets in each “Amount to Order” cell formula in the ordering guide sheet to get the correct total for an ingredient. I think a formula with ten to fifteen VLOOKUPs might be manageable and stay within the formula length limit Numbers impose (not sure what the limit in Numbers is but I reckon it is not as high as the 16k characters limit for a formula in Excel).

Note that you could avoid manually creating a different search formula for each ingredient in the ordering guide sheet if you use the absolute referencing wisely. After creating a proper formula for only one of the ingredients, you can copy & paste it to all other “Amount to Order” cells in the ordering guide sheet to get the total for each ingredient.

If you actually run into the problem of too-long formulas in the ordering guide sheet, you may consider having more than one recipe in one sheet and using the SUMIF or the SUMIFS functions instead of VLOOKUP.

Good luck!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .