Removing header row in Google sheets query pivot

Question

While removing the header row in a Google sheets query function seems straightforward, I don't see any suggestions on removing the header row when the query involves a pivot. For reference, I am giving a link with viewing rights to a spreadsheet which replicates the structure of the one I am working with.

The query is in cell C4 of the tab "Query sheet" and reads as follows:

= QUERY('Class hours'!A2:C11,"select sum(C) WHERE A='" & A5 & "' group by A pivot B")

The aim is to remove the row headers in cells C4:E4 in this sheet so that this function can be copied down column C to include all names in Column A with corresponding Sum (C) in the rows. Thanks for your attention.

player0 player0 · Accepted Answer · 2020-08-18T12:37:37

try this:

=QUERY(QUERY('Class hours'!A2:C11,
 "select sum(C) 
  where A = '"&A5&"' 
  group by A 
  pivot B"), 
 "offset 1", 0)

or even this in your case:

=INDEX(QUERY('Class hours'!A2:C11,
 "select sum(C) 
  where A = '"&A5&"' 
  group by A 
  pivot B"), 2)

Removing header row in Google sheets query pivot

1 Answers