How can I create a dynamic list of necessary ingredients in Google Sheets?

Other Questions Google Sheets
1

I’m working on compiling a list of recipes from Stardew Valley, detailing the required ingredients for each dish, the method of unlocking the recipe, and the ingredients for the recipes I haven’t yet prepared. For instance, if a total of 4 blueberries is necessary, I’d like it to indicate 4 blueberries needed. Whenever I update my spreadsheet to mark a recipe as completed, the quantity of blueberries I still need should automatically adjust accordingly.

Here’s a simplified version of the table I’ve constructed:

Status Dish Ingredients Quantity Source
Cooked Fried Egg Egg (Any) 1 Starter
Not Cooked Omelet Egg (Any) 1 Queen of Sauce 28 Spring, Year 1
Not Cooked Omelet Milk (Any) 1 Queen of Sauce 28 Spring, Year 1

I’ve successfully created a list of unique ingredients using the
=UNIQUE(C2:C203) and =SUMIF(C2:C203, G2:G, D2:D203) functions, generating a count of each unique ingredient I need. I’ve attempted to utilize the SUMIFS function to check the cooking status of meals with
=SUMIFS(C2:C203, G2:G203, D2:D203, A2:A203, 'Not Cooked'), but it continually returns zero. How can I resolve this and have my ingredient list update as intended?

2

I’ve encountered similar issues when working with conditional counts in spreadsheets. Here’s a solution you might try. Instead of referencing the entire column for sumif/sumifs conditions, make sure your range sizes match exactly, otherwise, the functions won’t perform as expected. Also, in the SUMIFS formula, ensure the criteria are correctly positioned. If your criteria range is ‘Status’ and you want to sum by ‘Quantity’, structure it as =SUMIFS(D2:D203, A2:A203, "Not Cooked", C2:C203, G2). This way, it ensures that the function is summing the ‘Quantity’ column where ‘Status’ is “Not Cooked” and ‘Ingredients’ match your list. If it still returns zero, check your data format; even small spaces or text format mismatches can interfere with formula results. Adjusting these aspects should get your dynamic list functioning properly.