Filter Rows while also doing 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.

As per the guidance from the previous Question i asked, i was able to do the sum of the columns. But in addition, is there a way to filter the rows in the output to exclude certain rows.

For example, i would like to exclude Team 2 from the data, so that the row is not displayed in the output at all as show below.

Sample data

Ramya

• Was in a rush when I answered. I'll write up some explanation and add a demo spreadsheet link for you to play with later today. Commented Aug 6 at 20:26
• Updated answer with extended formula as well as link to example spreadsheet. Anyone can edit it anonymously so you may wish to make your own local copy in Drive in case the internet destroys it. I'll add additional explanation shortly. Commented Aug 8 at 4:55

Add a `where` clause, like this:

``````=query(
Data!A1:Y,
"select A, sum(B), sum(C), sum(G)
where A is not null and A <> 'Team2'
group by A
order by sum(B) desc",
1
)
``````

See the query language help page.

Query Formula

• Place this formula in C1 of a different sheet than Data.
• Teams to exclude in A2:A
``````=INDEX(LET(
data,Data!A1:F, valT,TOCOL(A2:A,1), vaL,TOCOL(B2:B,1),
off,OFFSET(data,,1,,COLUMNS(data)-1), colO,COLUMN(off),
reL,REGEXMATCH(INDEX(off,1), "(?i)"&JOIN("|", vaL)),
colN,IF(COUNTA(vaL), FILTER(colO, reL), INDEX(colO)),
sumQ," SUM(Col" & JOIN(")," & " SUM(Col", colN) & ")",
colQ,IF(COUNTA(valT), "AND ("& JOIN(" AND ",
INDEX("Col1 <> '" & valT &"'"))&") ",),
IFNA(QUERY(data, "Select Col1,"& sumQ &
" Where Col1 <> '' "& colQ & " Group by Col1"))))
``````

Link to sheet Query of document 'WebApps Question 176666'

Query_Extended Formula

• Adds the ability to toggle between including/excluding Team and Heading search strings
``````=INDEX(LET(
data,Data!A1:F, onT,A2, onL,D2, inT,A3, inL,D3,
valT,TOCOL(B4:B,1), vaL,TOCOL(E4:E,1), sumC," SUM(Col",
delT,IF(inT," OR ", " AND "), eqT,IF(inT, " = '", " <> '"),
off,OFFSET(data,,1,,COLUMNS(data)-1), colO,COLUMN(off),
reL,REGEXMATCH(INDEX(off,1), "(?i)"&JOIN("|", vaL)),
colN,IF(onL*COUNTA(vaL),
FILTER(colO, IF(inL, reL, NOT(reL))), INDEX(colO)),
sumQ,sumC & JOIN(")," & sumC, colN) & ")",
colQ,IF(onT*COUNTA(valT), "AND ("& JOIN(delT,
INDEX("Col1"& eqT & valT &"'"))&") ",),
IFNA(QUERY(data, "Select Col1,"& sumQ &
" Where Col1 <> '' "& colQ & " Group by Col1"),
{"All results excluded by column label filter!";""})))
``````

Link to sheet 'Query_Extended' of document 'WebApps Question 176666'