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

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

Ramya

# 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"))
``````

# 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"
``````

• Thank you!! The formula works great! Commented Jul 24 at 13:08
• Is it possible to include a fliter in this formula to exclude the rows which have certain value. For example, i want to exclude "Building H" from the Pivot. Commented Jul 29 at 12:15
• @Ramya You should ask a new question for that functionality. When you do, post a link here in the comments if you want me to be notified. I would approach it a little differently and I'll link an example spreadsheet for you. Commented Jul 29 at 19:07
• thanks for the advise. Have posted the question here - webapps.stackexchange.com/questions/176666/… Commented Aug 5 at 12:52

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 ")`