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