SUM columns based in column header values as part of Select statement in Query function in Google spreadsheets

Question

I am trying to set up a pivot table using Query function in Google Spreadsheets, so that this can be used for various different reports.

Below is the sample data i have.

I already have this below Query function which helps me to pull the relevant columns and the sum of certain columns as well.

=QUERY(Data!$A$1:$Y$73, "Select A, SUM(B), SUM(C), SUM(G) Group by A Order by SUM(B) DESC ")

Giving the below output

But in the above i would like to make the "SUM" function a bit more dynamic. So instead of referencing to Column B,C and G, i would like the query to sum any columns that has "Q2" in its heading. is it possible to do that?

Please find the below link for the sample data.

Sample data

Thanks in advance!!

Ramya

Answer 1

Formula

=LET(rng,Data!A1:Y73, str,"Q2", txt," SUM(Col",
  cols,FILTER(COLUMN(rng), SEARCH(str,INDEX(rng,1))), 
  qTxt,txt & JOIN(")," & txt, cols) & ")",
  QUERY(rng, "Select Col1,"& qTxt &" Where Col1 <> ''
    Group by Col1 Order by "& txt & INDEX(cols,,1) & ") DESC")) 

COLUMN  FILTER  INDEX  JOIN  LET  QUERY  SEARCH  SUM

Explanation

1. cols uses FILTER and SEARCH to return the list of column numbers that contain the text string str in the first row of the range rng:

   FILTER(COLUMN(rng), SEARCH(str, INDEX(rng,1)))
   

2. txt stores "), SUM(Col" which is used by qTxt.

3. qTxt stores the result of JOIN combining the column numbers cols delimited with ")," & txt, which is equivalent to "), SUM(Col". JOIN's result has another instance of txt prepended and a closing parenthesis ) is also added.

   txt & JOIN(")," & txt, cols) & ")"
   

4. The QUERY then incorporates qTxt and also uses the first column in cols as the sort column.

   "Select Col1,"& qTxt &" Where Col1 <> ''
    Group by Col1 Order by "& txt & INDEX(cols,,1) & ") DESC "
   

   Which in my example using str="Q2" is equivalent to:

   "Select Col1, SUM(Col5), SUM(Col9), SUM(Col13), SUM(Col17), SUM(Col21)
     Where Col1 <> '' Group by Col1 Order by SUM(Col5) DESC"
   

   
   

   

Answer 2

You can view this formula: =QUERY(A1:G,"Select Col1,"&ArrayFormula("SUM(Col"&TEXTJOIN(", SUM(Col",1,IFERROR(IF(SEARCH("Q2",B1:G1),COLUMN(B1:G1)&")",))))&" Where Col1 is not null Group by Col1 ")