Google Sheet: How do I pass in ARRAYFORMULA into QUERY?

Question

I want to pass in ARRAYFORMULA into the QUERY so that I can get the sum without splitting the two functions apart.

I want to do this but getting an error ("unable to parse query string for function query parameter"):

=QUERY(ARRAYFORMULA(SPLIT(O5:O7, " to ")),"select sum(O),sum(P)")

Example:

This is the input:

1.080 to 3.240
0.771 to 2.312
0.721 to 2.164

Above input is pass in as O5:O7. Using this function, it will split into 2 columns and extract the number:

=ARRAYFORMULA(SPLIT(O5:O7, " to ")

As result, shown as following:

1.08    3.24
0.771   2.312
0.721   2.164

The above result will be pass into as O18:P20. This function will give the sum of each columns:

=QUERY(O18:P20,"select sum(O),sum(P)")

Result as the following:

sum     sum 
2.572   7.716

Could I pass in the arrayformula's result into the query?

Harun24hr · Accepted Answer · 2022-11-22 09:24:59Z

1

Use BYCOL() lambda function after splitting. Try-

=BYCOL(INDEX(SPLIT(P5:P7," to ")),LAMBDA(x,SUM(x)))

Or QUERY() function like-

=QUERY(INDEX(SPLIT(P5:P7," to ")),"select sum(Col1),sum(Col2)")

answered Nov 22, 2022 at 9:24

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

halfer · Accepted Answer · 2024-09-02 22:19:39Z

0

Yes:

=ARRAYFORMULA(QUERY(SPLIT(O5:O7, " to "), "select sum(Col1),sum(Col2)"))

If you don't need labels:

=INDEX(QUERY(SPLIT(O5:O7, " to "), "select sum(Col1),sum(Col2)"), 2)

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Nov 22, 2022 at 13:01

player0

131k14 gold badges91 silver badges149 bronze badges