Summing quantities based on like criteria?

[aburnce]

Not sure where to look for this.

I have a spreadsheet containing recipes and the ingredients used fo
them. I want to sort by ingredients (peanuts might come up in
recipes, for instance, and each has a quantity listed in
corresponding cell), then sum the total quantity of that ingredien
needed across all recipes that contain it. Not sure if this is don
with a formula, a pivot table, or something else. Any help would b
appreciated.

If this is not clear, my end result would be that I have an easy Exce
way to just list the quantities of all the ingredients needed for
bunch of recipes, then go shop for the total quantity of eac
ingredient that I need. I'm a backpacker trying to put together a mea
plan for a week-long trip.

See the attached sheet - I'm trying to work with the Ingredients an
Total Qty columns.

THANKS! :

+-------------------------------------------------------------------
|Filename: meal plan spreadsheet.zip
|Download: http://www.excelforum.com/attachment.php?postid=4605
+-------------------------------------------------------------------

 

[Biff]

Hi!

First thing you need to do is to convert the TEXT numbers in column D to
NUMERIC numbers.

Select cell A1
Goto Edit>Copy
Select the range D491
Goto Edit>Paste Special>Add>OK

Now, create a list of the unique ingredients.

Select the range C3:C91
Goto Data>Filter>Advanced Filter
Select Copy to another location
Copy to: $M$3
Select Unique records only
OK

Now, get the total needed.

Enter this formula in N4:

=SUMIF(C$4:C$91,M4,D$4$91)

Copy down to N56.

Biff

 

[aburnce]

Thank you! That's exactly what I needed.

For anyone who might care...I decided that it would be easier to
concatenate the Ingredients and Measure columns, then apply the filter
to that in making my shopping list. This allows my unique list to
include the same item listed in the various recipes with different
measures (i.e. 1 recipe calls for 3 tsp of cinnamon, one for 2 tbl).
Then when I do the SUMIF function, I have my measures visible and can
more easily identify which ingredients with unlike measures need to be
converted and added together.

Final version is attached. Thanks, Biff!


+-------------------------------------------------------------------+
|Filename: meal plan spreadsheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4606 |
+-------------------------------------------------------------------+

 

[Biff]

You're welcome!

Biff

 

[aburnce]

I'd like to take this spreadsheet one step further and add the ability
to select which recipes I want to shop for. I would just build recipes
into the spreadsheet, then have an INCLUDE column with Y/N values
indicating whether to include a particular recipe for a given trip. I
would then somehow apply the Advanced Filter only to the items with Y
selected, and would SUMIF only the quantities where Y is selected. So
I guess there are two things here I need to know how to do:

1.) Apply the Advanced Filter based only on items in a list that meet a
criteria, rather than the whole list

2.) Create a SUMIF statement that uses TWO criteria: one for whether
the ingredient name matches, and one for whether Y is selected in the
INCLUDE column.

Thanks in advance for the help.

Alan

 

[aburnce]

bumpity bump