Summing quantities based on like criteria?

[aburnce]

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!

[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 D4:D91
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:D$91)

Copy down to N56.

Biff

"aburnce" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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!

[Biff]

You're welcome!

Biff

"aburnce" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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