I have 2 spreadsheets:
- the first one with some metrics and dates – https://docs.google.com/spreadsheets/d/1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as/edit?gid=0#gid=0
- the second one where the data is inverted and grouped – https://docs.google.com/spreadsheets/d/1KEFsZLoK1GyteKU2smIK3Ac6DIHcx5oOmnvnFTQgPr0/edit?gid=0#gid=0
I need to get the total number for some metrics specified on the second spreadsheet using “+” as a separator. Now I use the following structure:
=INDEX(IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:DN"),MATCH(B$1,IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:A"),0),MATCH(CHOOSECOLS(SPLIT($A4,"+"),1),IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!1:1"),0))+INDEX(IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:DN"),MATCH(B$1,IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:A"),0),MATCH(CHOOSECOLS(SPLIT($A4,"+"),2),IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!1:1"),0))+INDEX(IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:DN"),MATCH(B$1,IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$2:A"),0),MATCH(CHOOSECOLS(SPLIT($A4,"+"),3),IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!1:1"),0))
The problem is that this formula needs to be changed depending on the number of metrics that need to be summed. Is there some way to optimize this formula so that it is universal for all fields, and also works for the entire array at once, instead of each cell separately?
Here’s one approach which you may adapt accordingly:
=let(Σ,importrange("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","A1:E10"),
map(A2:A4,lambda(Λ,map(B1:G1,lambda(Δ,sum(ifna(filter(filter(Σ,choosecols(Σ,1)=Δ),xmatch(chooserows(Σ,1),split(Λ,"+"))))))))))
1
You may also try the following formula-
=MAKEARRAY(ROWS(A2:A4),COLUMNS(B1:G1),LAMBDA(r,c,
LET(x,IMPORTRANGE("1jNsgbu-IdkxF_vS6SxXR15rJiFZMpPsktcy6lV37_as","Sheet1!$A$1:$DN"),
y,CHOOSECOLS(x,1),
z,CHOOSEROWS(x,1),
SUM(CHOOSECOLS(FILTER(x,y=CHOOSECOLS(B1:G1,c)),INDEX(XMATCH(SPLIT(CHOOSEROWS(A2:A4,r),"+"),z)))))))