2

Based on feedback in comments, this is a complete rewrite of the post, including the title.

I am trying to create a Google Sheets document with 3 sheets (tabs). Two of the sheets are static data while the third is mostly populated with formulas based on the other two sheets. I can do most of what I need, but there is one thing I am trying to do, but can't seem to get right.

The first sheet with static data is the "Recipes" tab. It contains all of the recipes for products. The only "products" are Bikes and Cars in these examples. It also contains the quantity of the output produced per process (so this can be more than 1) along with the required input materials and the quantity required of those materials per process cycle.

So if a bike uses 2 wheels, and you're making 10 bikes per process, you will need 20 wheels total.

      A        B                  C              D
1     Output   Output Quantity    Input          Input Quantity
2     Bike     10                 Wheel                  20
3     Bike     10                 Breaks                 20
4     Bike     10                 Handle Bar             10
5     Bike     10                 Seat                   10
6     Car      5                  Wheel                  20
7     Car      5                  Breaks                 20
8     Car      5                  Seat                   20
9     Car      5                  Steering Wheel         5

The second sheet with static data is the "Item Info" sheet. It contains a list of all items (both inputs and outputs) along with the size and price of the part/product. The order can not be relied upon and the same items may be used multiple times in different recipes:

      A               B               C
1     Product         Size Per Unit   Price Per Unit
2     Bike            50              200
3     Car             1000            25000
4     Wheel           10              50
5     Breaks          5               25
6     Handle Bar      5               30
7     Seat            25              50
8     Steering Wheel  15              100

What I'm trying to get, is a in a third sheet, to be able to list multiple products I can create (So either Bikes or Cars) and then on the same row pull in the total size of all the inputs for that item.

So the final result in the third sheet "Production Stats" should look like this:

        A       B
1       Output  Total Input Size
2       Bike    600         
3       Car     875 

Where the "A" column is static, and the "B" column is a formula.

I am trying to get a formula which achieves the following: Uses the value from 'Production Stats'!A2:A to do a look up against 'Recipes'!A2:A to find all the items used in the recipe and the quantity used (So 'Recipes'!C2:D9). Then for each "Input" do another lookup against 'Item Info'!A2:A and get the size of those items ('Item Info'!B2:B). For each "Input" it should multiple the values from 'Recipes'!D2:D with the values from 'Item Info'!B2:B. Finally, it should add up all those values.

So if we were processing the formulas for 'Production Stats'!B2 the math would be:

Wheel     : 20 * 10 = 200
Breaks    : 20 * 5 = 100
Handle Bar: 10 * 5 = 50
Seat      : 10 * 25 = 250
Then      : 200 + 100 + 50 + 250 = 600

So I know something like this would be able to get the "Input Quantity" for each input, each on its own row:

=filter(Recipes!D$2:D, Recipes!A$2:A=index(A2:A,match(A2,A2:A,0),1))

And something like this would be able to get the size of each "Input", spread across 4 rows:

=filter('Item Info'!B$2:B, 'Item Info'!A$2:A=filter(Recipes!C$2:C, Recipes!A$2:A=index(A1:A,match(A1,A1:A,0),1)))

So I was thinking something like either of these might work:

=SUM(filter(Recipes!D$2:D, Recipes!A$2:A=index(A2:A,match(A2,A2:A,0),1))*filter('Item Info'!B$2:B, 'Item Info'!A$2:A=filter(Recipes!C$2:C, Recipes!A$2:A=index(A2:A,match(A2,A2:A,0),1))))

And:

=filter(SUM(Recipes!D$2:D*filter('Item Info'!B$2:B, 'Item Info'!A$2:A=filter(Recipes!C$2:C, Recipes!A$2:A=index(A2:A,match(A2,A2:A,0),1)))), Recipes!A$2:A=index(A2:A,match(A2,A2:A,0),1))

But the first returns the value 200, which is the value for the first sequential "Input", excluding the rest. Then the second has an error "Array arguments to MULTIPLY are of different size". But when I use each argument on their own, they return the same number of rows.

So while I thought nested filter() calls was the way to achieve what I want, I realize it may not be the best way. So I'm open to alternatives. Is what I'm trying to do even possible? If so, what's the best way to go about it?

Much appreciation!

2
  • To be clear up front, I am not volunteering to work on this issue myself. But I will suggest that rather than telling the volunteer community here what you are doing that isn't working with your formulas, just clearly explain the data goal (as if neither you nor anyone else knows anything about formulas). Clearly indicate which pieces of data you want to manually enter (and where), which pieces of data you want returned (and where), and how you arrived at each of those returns. Then let whoever here is willing decide the best formulaic approach (which is likely not FILTERS within FILTERS).
    – Erik Tyler
    Commented Aug 29, 2022 at 7:40
  • 1
    @ErikTyler Thank you for the feedback. I tried to be detailed and outline what I have, what I expect, what I tried and what I'm actually getting. But I probably provided too much detail and made it overwhelming. Also, you make a great point, that nested filters() may not be the solution I need. So once I get home I'll revise my question and remove as much unnecessary information as I can and instead of asking how to use nested filters(), I'll simply ask what is the best way to achieve what I desire. Thank you for taking the time to provide feedback.
    – d.lanza38
    Commented Aug 29, 2022 at 14:19

1 Answer 1

1

try for C15 :

=filter('Item Info'!B6:B, 'Item Info'!A6:A=filter(Recipes!C6:C, Recipes!A6:A= index(A11:A,match(A15,A11:A,0),1)))

(unfortunately I am not prepared on the topic covered in your sheet, so the rest will have to be done by yourself)

2
  • Thank you @Daniele . This seems to work, but I see you're just moving the searchable range on the filters from the second column to the sixth column. Should the notation of "A2:A" allow the FILTER() to look in the entire A column starting with row 2? I can't reliably ensure the order of items in the "Item Info" sheet And in some cases the same items can be used for multiple recipes. So a sequential list isn't possible for my use-case. Is there a reason why the filter can't just search for matches in the entire column and has to be positioned to the exact row?
    – d.lanza38
    Commented Aug 27, 2022 at 20:33
  • Note: I updated my question with much more detail. And also changed the data to something more generic and intuitive. I wasn't trying to focus on the specific data, but instead the FILTER function and its behavior. But, I realized a more common and intuitive data set would make the relationships easier to grasp, allowing a greater focus on the functionality.
    – d.lanza38
    Commented Aug 27, 2022 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.