I have two spreadsheets, in 1 I have several recipes with the total. In the other sheet the client can choose 2 recipes. I would like to SUM the total of each selected recipe, but I don't know how to do this in the google spreadsheet. I thought of the following algorithm.
SELECTED RECIPE (COLUMN + 1):(ROW + 4)
i.e If the client select
RECIPE 1 (A + 1) = B : (1 + 4) = 5 TOTAL = B:5
RECIPE 3 (A + 1) = B : (7 + 4) = 11
TOTAL = B:11
use:
=ARRAYFORMULA(SUM(QUERY(SPLIT(TRIM(FLATTEN(
SPLIT(QUERY(FLATTEN(IFERROR(IFNA(REGEXEXTRACT(Sheet1!A1:E,
TEXTJOIN("|", 1, G1:I1))), "♠"&Sheet1!A1:E&"♦")),,9^9), "♦"))), "♠"),
"select Col2 where Col1 matches '"&G1&".+|"&H1&".+'", 0)))
You data is really not set up in a readable format for excel. If you want it to be easier to scale (such as if you have a lot of recipes), you should set up the data to look like this and use this formula
=iferror(index(F19:F20,match(A23,A19:A20,0)),0)+iferror(index(F19:F20,match(B23,A19:A20,0)),0)
If you really want to use your current data format, you could use an if statement for each recipe
=if(A14="Recipe 1",B5,if(A14="Recipe 2",E5,if(A14="Recipe 3",B11,if(A14="Recipe 4",E11,0))))+if(B14="Recipe 1",B5,if(B14="Recipe 2",E5,if(B14="Recipe 3",B11,E11,if(B14="Recipe 4",E11,0))))
