Not sure where to look for this.

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

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

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

THANKS! :)


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

--
aburnce
------------------------------------------------------------------------
aburnce's Profile: http://www.excelforum.com/member.php...o&userid=33288
View this thread: http://www.excelforum.com/showthread...hreadid=531137

Hi!

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

Select cell A1
Goto EditCopy
Select the range D4:D91
Goto EditPaste SpecialAddOK

Now, create a list of the unique ingredients.

Select the range C3:C91
Goto DataFilterAdvanced 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:D$91)

Copy down to N56.

Biff

"aburnce" wrote in
message ...

Not sure where to look for this.

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

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

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

THANKS! :)


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

--
aburnce
------------------------------------------------------------------------
aburnce's Profile:
http://www.excelforum.com/member.php...o&userid=33288
View this thread: http://www.excelforum.com/showthread...hreadid=531137

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

--
aburnce
------------------------------------------------------------------------
aburnce's Profile: http://www.excelforum.com/member.php...o&userid=33288
View this thread: http://www.excelforum.com/showthread...hreadid=531137

You're welcome!

Biff

"aburnce" wrote in
message ...

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

--
aburnce
------------------------------------------------------------------------
aburnce's Profile:
http://www.excelforum.com/member.php...o&userid=33288
View this thread: http://www.excelforum.com/showthread...hreadid=531137

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
------------------------------------------------------------------------
aburnce's Profile: http://www.excelforum.com/member.php...o&userid=33288
View this thread: http://www.excelforum.com/showthread...hreadid=531137

bumpity bump


--
aburnce
------------------------------------------------------------------------
aburnce's Profile: http://www.excelforum.com/member.php...o&userid=33288
View this thread: http://www.excelforum.com/showthread...hreadid=531137