CODEWITHSUNDEEP
google-sheetssumifsarray-formulas
Wilson Wee
Wilson Wee

Reputation: 21

Making an ArrayFormula from QUERY

How do I convert this:

=if(ISNA(sum(QUERY(importrange("1QNYU1Kb1rtx-n4tMRamHJuvYt_9kGMfhGZZs_opuJr0","Transfer!C$2:E"),"SELECT Col3 WHERE Col1 = '" & Sheet1!B$2:B & "' ",0))),"0",sum(QUERY(importrange("1QNYU1Kb1rtx-n4tMRamHJuvYt_9kGMfhGZZs_opuJr0","Transfer!C$2:E"),"SELECT Col3 WHERE Col1 = '" & Sheet1!B$2:B & "' ",0)))

into the array formula in Google Sheets?

Upvotes: 1

Views: 700

Answers (1)

player0
player0

Reputation: 1

in my opinion, it would be pretty much an overkill if it would be possible. instead I recon to do this:

  • use side columns (or create a new sheet in PRC Records spreadsheet) and paste this formula there: 
=QUERY({Transfer!A1:H}, "select Col3, sum(Col5) 
                         where Col3 is not null 
                         group by Col3 
                         label sum(Col5)'FG Qty'", 1)

0

  • and then use this formula in J2 cell over on the MO Listing spreadsheet:
=ARRAYFORMULA(IF(LEN(B2:B), IFERROR(VLOOKUP(B2:B, 
 IMPORTRANGE("1QNYU1Kb1rtx-n4tMRamHJuvYt_9kGMfhGZZs_opuJr0", 
             "Transfer!L2:M"), 2, 0), 0), ))

Upvotes: 1

Related Questions