# Summing Values depending on duplicates

Hi there!

I’m trying to create a shopping list based on recipes that contain multiple ingredients. However since multiple recipes can contain the same ingredient and in the different amounts, I was wondering is there a way to aggregate these different amounts under one ingredient value so that ingredients only appear once in the shopping list with a combined value?

(I tried Dedupe but it doesn’t sum up any values)

Thanks!

The recipe base I made accomplishes this:

Basically, it has an `[Ingredients]` table which uses Rollup fields to add up the total amount of ingredients needed for the recipes planned for the current week. For this to work, `[Recipe Ingredients]` records act as a “middle man” between the `[Recipe]` and `[Ingredients]` tables.

1 Like

Your meal planner looks great and it looks like it solves the duplication problem. Can you quickly explain the logic behind how using a middle man table prevents duplication? Thanks so much.

Sure. Lets take the Chicken Noodle Recipe as an example:

To make Chicken Noddle Soup, you need several ingredients in varying quantities (i.e. 8 oz egg noodles, 2 lb chicken breasts, etc.). Now, I could store this all in a Long Text field, but then I couldn’t make an automatic shopping list because Airtable wouldn’t be able to distinguish the quantity from the ingredient, and between each line of text.

So instead, I made a `[Recipe Ingredients]` table to work as a “junction table,” which means it pairs one record from one table (`[Recipes]`) with another record in another table (`[Ingredients]`) in order to store more information about the union of those two tables.

So, I know that to make the soup I need 2lbs of chicken and 8oz of noodles. This is recorded in the `[Recipe Ingredients]` table like this:

• `{Recipe}` || `{Quantity}` || `{Unit}` || `{Ingredient}`
• `Chicken Noodle Soup` || `2.000` || `lb` || `chicken breast`
• `Chicken Noodle Soup` || `8.000` || `oz` || `egg noodles`
• etc.

Basically, every time an ingredient is used in a recipe, it gets its own record in the `[Recipe Ingredients]` table describing how much is needed. Therefore, each `[Recipe Ingredient]` record is only tied to one `[Recipe]` record and only one `[Ingredient]` record each.

Because my base is also a meal planner, I have another very simple table which asks for a date and a recipe so I can plan meals out ahead of time. Now that I have dates associated with recipes, I can use a formula to filter down to all the relevant `[Recipe Ingredients]` records which will be needed that week.

The `[Ingredients]` table, with the help of another formula, is then able to say: Egg noodles are needed this week, add up all the `[Recipe Ingredient]` records containing egg noodles if I selected that recipe for this week.

2 Likes