I want to sum value from workbook A in sheet A, but the problem is Name in Workbook B in 1 column does not split. I use this formula to match the criteria & use split, but only cell with "Dian" will calculate.
=SUM(INDEX(
IMPORTRANGE("Workbook A", K10 & "!I:I"),
MATCH(
1,
(G12=IMPORTRANGE("Workbook A", K$10 & "!H:H"))*
(REGEXMATCH(IMPORTRANGE("Workbook A", K$10&"!G:G"),TEXTJOIN("|", TRUE, SPLIT(H12, ", ")))
),
0
)
))
Try this formula:
=query({
ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({
query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col2 where Col2 is not null label Col2 ''")&"×",
query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col1 where Col1 is not null label Col1 ''")&",",
{SEQUENCE(ROWS(query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col2 where Col2 is not null label Col2 ''")),1,2)},
query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col3 where Col3 is not null label Col3 ''")},
"select max(Col2) where Col2 <> ',' group by Col3 pivot Col1"),,9^9)), "×")), ",$", )),
query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Sum(Col3) where Col1 is not null group by Col2 label sum(Col3) ''")
},
"Select Col1, Col2, Col3 label Col1 'Category', Col2 'Name', Col3 'Total'")
LOGIC
The formula is based answer in How to create a comma separate aggregate in Google Sheets?. In that case, the data was a given and the answer employed data in two existing columns - {A2:A&"×", B2:B&",", ROW(A2:A)}
.
In this case, there are a couple of significant differences:
IMPORTRANGE
IMPORTRANGE
formula are used for each columnA2:A&"×"
becomes importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")},"select Col2 where Col2 is not null label Col2 ''")&"×"
ROW(B2:B)
becomes {SEQUENCE(ROWS(query({importrange("https://docs.google.com/spreadsheets/d/1phYtxKeqe89llDTNjS0JYJsag7l8VumDeBfQF9Z9uHU","SheetA!A1:C")}
IMPORTRANGE
Columns A:C with an argument to sum Column C and order by Column B:select Sum(Col3) where Col1 is not null group by Col2
RESULTS
This applies the logic described in the answer by @player0 in How to create a comma separate aggregate in Google Sheets?