dimanche 8 septembre 2019

Add IF statement within QUERY

I'm trying to add the following IF statement to an existing QUERY, but only end up with a variety of different errors with each attempt.

Here is the original QUERY:

=ARRAYFORMULA(QUERY({projectMaterialsPre!A3:A,projectMaterialsPre!B3:B*1,projectMaterialsPre!C3:K}, 
 "select Col1,sum(Col2),Col3,Col5,Col6,Col7,Col8,Col9,Col10,Col11 
  where Col1 is not null 
  group by Col1,Col3,Col5,Col6,Col7,Col8,Col9,Col10,Col11
  label sum(Col2)''", 0))

And this is the IF statement I would like to include:

IF(LEN(projectMaterialsPre!B3:B),ROUNDUP(IF(projectMaterialsPre!C3:C<>projectMaterialsPre!E3:E,projectMaterialsPre!B3:B/projectMaterialsPre!F3:F,projectMaterialsPre!B3:B),),)

My novice brain tells me to just edit it to sum(Col2),"&[IF FORMULA]&" Col3. (The more I look at it, I probably need to replace sum(Col2))

I may be going about this all wrong (obviously, or it would just work). The "translation" to what it means to the QUERY columns would be: IF(LEN(COL2),ROUNDUP(IF(COL3<>COL5,COL2/COL6,COL2),),)

My sheet

Aucun commentaire:

Enregistrer un commentaire