Google Sheets – How to Multiply a Query Column by a Cell Reference

formulasgoogle sheetsgoogle-sheets-query

Below is the query that I use, and it works:

=QUERY(Input!A2:I33;
 "SELECT '000', A, B, C, D, E, ' ', F, '  ', '   ', '    ', G, '     ', '      ', '       ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"', 0.6*H 
  WHERE I='I' 
  LABEL '000' '', ' ' '', '  ' '', '   ' '', '    ' '', '     ' '', '      ' '', '       ' '', '"&TEXT(Input!L8;"dd/mm/yyyy")&"' '', 0.6*H ''")

My question is: How can I multiply H by a cell refference? For instance, here's what I tried, but it did not work ("Input!L9" reffers to a cell with the value 0.6):

=QUERY(Input!A2:I33;
 "SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"', "&Input!L9&"*H 
  WHERE I='I' 
  LABEL '000' '', ' ' '', ' ' '', ' ' '', ' ' '', ' ' '', ' ' '', ' ' '', '"&TEXT(Input!L8;"dd/mm/yyyy")&"' '', "&Input!L9&"*H ''")

Best Answer

  • try H*"&Input!L9&" with label H*"&Input!L9&"''

=QUERY(Input!A2:I33,
 "select '000',A,B,C,D,E,' ',F,'  ','   ','    ',G,'     ','      ','       ','"&TEXT(Input!L8,"dd/mm/yyyy")&"',H*"&Input!L9&" 
  where I='I' 
  label '000''',' ''','  ''','   ''','    ''','     ''','      ''','       ''','"&TEXT(Input!L8,"dd/mm/yyyy")&"''',H*"&Input!L9&"''")

note: EU syntax will need to have SUBSTITUTE(Input!L9; ","; ".") instead of Input!L9

Related Topic