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

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.

Please find the below link for the sample data.

Sample data

Thanks in advance!!

Ramya

Answer 1

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.

Answer 2

Link to example Google spreadsheet that can be edited anonymously.

Query Formula

• Place this formula in C1 of a different sheet than Data.
• Teams to exclude in A2:A
• Headings to include B2:B

=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'