Dynamic column selection using Query in Google Sheets

Question

I have a spreadsheet that is intended to tally up hours worked by location and generate amounts to invoice. I've figured out a (fairly brittle) way to achieve this using SUMPRODUCT on a sorted list of resources. However, this clearly doesn't scale.

It occurred to me that I could use a QUERY function along with SUMPRODUCT and free myself from the need to force an artificial structure onto the data set. However, since this formula needs to be copied across each column in my spreadsheet and since the column selector is part of the SELECT statement string it doesn't dynamically adjust as I copy the formula across each cell in the row. I tried building a formula using COLUMN() to dynamically build the SELECT statement but kept getting parse errors.

Here is the sheet: https://docs.google.com/spreadsheets/d/1EK2UcgsBqzb3Pfky8SALlTSEdeYvviv3QeyEPDu2mF0/edit?usp=sharing

Can someone help me out?

Answer 1

try:

=INDEX(QUERY(QUERY(SPLIT(FLATTEN(
 IF(C3:K10="",,B3:B10&"×"&C3:K10*L3:L10&"×"&C2:K2)), "×"), 
 "select Col1,sum(Col2) 
  where Col2 is not null
  group by Col1
  pivot Col3"),
 "offset 1", ))