Sum Value from other workbook with criteria

Question

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

Any help?

Answer 1

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:

• data is imported from another spreadsheet using IMPORTRANGE
  • so IMPORTRANGE formula are used for each column
  • for example:
    • A2: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")}
• there is a third column to be summed
  • resolved with a query using the entire 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?