SUM the total in different spreadsheets

Question

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

Answer 1

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)))

spreadsheet demo

Answer 2

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))))