Google Sheets – QUERY Formula Excludes Results with New Line

To suppress the header, end the query string with label sum(A) ''.

The blank spot below the header comes up because the column B, besides the letters a,b,c, also contains a bunch of blank cells, which form a group of their own. Since you don't want them, add the clause where B<>''.

End result:

=query(A1:B, "select sum(A) where B<>'' group by B label sum(A) ''")

(Case doesn't matter: I prefer lowercase because more text fits into the same horizontal space.)

The arrayformula(query(...)) combination is not supported; there is no concept of a query processing an array of arrays or executing an array of query strings.

You have two options: (a) repeat query on every row; (b) use vlookup to retrieve columns of data, as explained below. For example:

=arrayformula(vlookup(E3:E, {A3:A, J3:J}, 2, false))

takes one element of E3:E at a time, finds this element in column A, and retrieves the corresponding element of column J. With this approach you would need six separate vlookups to get the columns J,I,H,G,F,E, but you won't need a separate command for each row.

A complication is that vlookup accepts only one search key, and you want to search by 4 parameters,

A='"&E3&"' and 
B='"&B3&"' and 
C="&C3&" and 
D="&D3&

This can be worked around by concatenating these into one search key: you can have a column like Z,

=arrayformula(E3:E & "|" & B3:B & "|" & C3:C & "|" & D3:D)

which concatenates four search parameters into one pipe-delimited search key. Do the same for the table to be searched, and then compare these keys using vlookup.