Sum rows if header and first column meet criteria in Google Sheets

Question

I have a table with first row as header and first column as week number, I need to sum all the data on every week that meets the criteria depending on the header name.

This formula only filter by header and week, but I can't summarize the data, I would like that the formula is "dynamic" because the header name section can change:

=QUERY(TRANSPOSE(A1:AA23),"SELECT * WHERE Col1 = 'M.O.'",1)

This is an example spreadsheet, at the end is the desired result: LINK TO EXAMPLE SHEET

user_5632629 · Answer

use:

=INDEX(QUERY(SPLIT(FLATTEN(IF(D2:24="",,D1:1&"×"&A2:A24&"×"&D2:24)), "×"), 
 "select Col1,sum(Col3) 
  where Col1 matches 'INDIRECTO|M.O.' 
  group by Col1 
  pivot Col2"))

Sum rows if header and first column meet criteria in Google Sheets

1 Answers