CODEWITHSUNDEEP
functiongoogle-sheetsgoogle-sheets-formula
MatiasDrejer
MatiasDrejer

Reputation: 21

Query function in Google Sheets simplification

I am looking for a way of simplifying this formula since it has to run in 17424 unique cells:

=IFERROR(SUMPRODUCT(QUERY('MASTER Accelo Data 1'!A:G;"Select G where (B>=date '2020-01-01' and B<=date '2020-01-31' and A matches '"&J2&"' and D matches '"&JOIN("|";$T$2:$T)&"')";0));0)+IFERROR(SUMPRODUCT(QUERY('MASTER Accelo Data 2'!A:G;"Select G where (B>=date '2020-01-01' and B<=date '2020-01-31' and A matches '"&J2&"' and D matches '"&JOIN("|";$T$2:$T)&"')";0));0)

where...

MASTER Accelo Data 1!G:G + MASTER Accelo Data 2!G:G = Cells to be summed

MASTER Accelo Data 1!B:B + MASTER Accelo Data 2!B:B= Dates to be checked

J2 = Customer ID

MASTER Accelo Data 1!A:A + MASTER Accelo Data 2!A:A = Customer IDs to be checked

T2:T = Array of owners

MASTER Accelo Data 1!D:D + MASTER Accelo Data 2!D:D = Owners to be checked

Link to dummy workbook: https://docs.google.com/spreadsheets/d/1hevSA0vl3YO81vaxfJ46TAyO-VkUhXKk8WRKY8268qY/edit?usp=sharing

Hoping you guys can help!

Upvotes: 1

Views: 36

Answers (1)

MatiasDrejer
MatiasDrejer

Reputation: 21

Solved using arrayformula:

=ARRAYFORMULA(
  QUERY(
   {{'MASTER Accelo Data 1'!A2:C;
     'MASTER Accelo Data 2'!A2:C}\
    HVIS.FEJL(
     LOPSLAG(
      {'MASTER Accelo Data 1'!D2:D;
       'MASTER Accelo Data 2'!D2:D};
      {"OQ"\"Apprentice";"OP"\"Apprentice";
       "DH"\"Associate";"KI"\"Associate";
       "UY"\"Intern";"YU"\"Intern";"ER"\"Intern";
       "WE"\"Intern";"KY"\"Intern";"VB"\"Intern";
       "KP"\"Management";"MD"\"Management";"KL"\"Management";
       "AS"\"Management";"SD"\"Management";"IGR"\"Pss";
       "GTI"\"Pss";"SS"\"Pss";"SQ"\"Pss";"VD"\"Pss";
       "AE"\"Senior";"A"\"Senior";"VE"\"Senior";
       "KJ"\"Senior";"KO"\"Senior";"LQ"\"Senior";
       "IU"\"Senior"};
      2;FALSK))\
    {'MASTER Accelo Data 1'!G2:G;
     'MASTER Accelo Data 2'!G2:G}};
   "select Col1, Sum(Col5) 
    where 
     Col3 is not null and
     Col2 >= date '2020-01-01' and
     Col2 <= date '2020-01-31'
    group by Col1
    pivot Col4
    label Col1 'ID'"))

Upvotes: 1

Related Questions