Google SpreadSheet Query: Can I remove column header?
Google SheetsFormulasGoogle Sheets-QueryGoogle Query-LanguageGoogle Sheets Problem Overview
I'm doing this query at my google spreadsheet:
=QUERY(H4:L35;"select sum(L) where H='First Week'"; -1)
But it returns a little table with "sum" as header and result below it. What I want is just the result! How I remove header? Can I?
Google Sheets Solutions
Solution 1 - Google Sheets
Try this:
=QUERY(H4:L35,"select sum(L) where H='First Week' label sum(L) ''")
Solution 2 - Google Sheets
=QUERY(QUERY(A1:D, "SELECT *", 1), "SELECT * OFFSET 1", 0)
The outer query: "SELECT * OFFSET 1"
excludes the first row (the header).
The inner query explicitly specifies one row of headers (via the third argument supplied to QUERY
), whilst the outer query specifies none.
Solution 3 - Google Sheets
=INDEX(QUERY(H4:L35;"select sum(L) where H='First Week'"; -1),2,1)
This just parses the returned array and selects the 2nd record returned in the first column.
You can also do this with the filter function which is less compute intensive.
=SUM(FILTER(L4:L35, H4:H35 = "First Week"))
Solution 4 - Google Sheets
Instead of labeling column names as blanks using ''
, you can omit all headers like this:
=QUERY(H4:L35,"select sum(L) where H='First Week'", 0)
Solution 5 - Google Sheets
I have a QUERY which is returning the top 3. I could not get this to work when returning multiple rows. I ended up just hiding the row with the formula and only the answers show now.
Solution 6 - Google Sheets
For queries using pivot, try using INDEX to remove headers from the pivoted columns.
=INDEX(QUERY('Class hours'!A2:C11, "select sum(C) where A = '"&A5&"' group by A pivot B"), 2)
Got the answer from this thread: https://stackoverflow.com/a/63468791/5424088
Solution 7 - Google Sheets
See the format here.
Example:
=QUERY(B4:C38,
"SELECT C, sum(B) where C!='' group by C label C 'Member', sum(B) 'Sum'"
)