2

I'm trying to eliminate (not hide) the column headings from the following QUERY(). The output from the =QUERY() must be in a single row.

=QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&E11&"'")

The current output is shown in images #1 and #2 below. The desired output is shown in image #3. Thanks for any help you can offer.

Image #1

enter image description here

Image #2

enter image description here

Image #3

enter image description here

1

2 Answers 2

3

To remove headers, you should refer to the data range A2:G rather than the full columns A:G, and explicitly set the headers parameter to zero.

Additionally, you need to use the label clause to omit automatic headers in any aggregated columns, like this:

=query( 
  myData!A$2:G, 
  "select sum(E), sum(F) 
   where D = '" & E11 & "' 
   label sum(E) '', sum(F) '' ", 
  0 
)
3
  • I did not find any difference or improvement between my formula and the one you recommended, neither as regards the columns, nor as regards the use of the third parameter of the QUERY () function. Those are additional unnecessary items.
    – Daniele
    Jun 11, 2022 at 16:23
  • 1
    Whether the two formulas give the same results depends on the data. When headers is missing, query() tries to guess the number of header rows, and may guess incorrectly. If it guesses incorrectly, values will be missing from the sum, or superfluous values will get included (in the event the headers contain numbers.) Ditto for a data range that includes rows that are not data but headers. Additionally, when headers is missing, and the column is mostly blank, query() may set the column type incorrectly. I see no reason to ignore these scenarios as that amounts to trying your luck. Jun 11, 2022 at 20:07
  • I'm a bit skeptical but when in doubt I prefer to believe your undisputed knowledge of the subject
    – Daniele
    Jun 12, 2022 at 0:52
0

try this one:

=QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&E11&"'label SUM(E) '', SUM(F) ''")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.