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

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?

CodePudding user response：

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)")

CodePudding user response：

yes:

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

if you dont need labels:

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