# Combine an array formula with query function

I want to know if there is a way that I can combine any of these 3 formulas.

Sample Sheet

I have in col. A3, this extracts the info in Data tab, and splits into rows: `=arrayformula(substitute(transpose(split(query(filter(substitute(substitute(Data!C2:C, " ", "_"), char(10), " " ),(Data!B2:B = "paid") + (Data!B2:B = "not paid")),"", 9^9)," ")),"_", " "))`

Then, this splits that col. into unit & name: `=ArrayFormula(if(len(A3:A), trim(regexextract(A3:A, "^(\d+)\s(.*)\$")),))` Also in col. F, I run =Value formula to convert D to values, because sum doesn't work on D.

And finally, this groups the data in E:D and sums them: `=QUERY(E3:F856,"SELECT E, SUM(F) GROUP BY E")`

Is there anyway I can combine any of these formulas to clean up the sheet and reduce the number of columns I use?

I've tried to embed the 2nd formula into query, in various amateur ways & these give #value error:

`=QUERY('*formula2*',"SELECT E, SUM(F) GROUP BY E")`

`=QUERY(E3:F856,"SELECT E, '*formula2*' SUM(F) GROUP BY E")`

And also, embed formula 1 into formula 2:

`=ArrayFormula(if(len(*'formula1'*), trim(regexextract(A3:A, "^(\d+)\s(.*)\$")),))`

If there is a way to clean up then great, but if there isn't then I'll have to deal with it.

• You may find that it is much easier to maintain those three separate formulas instead of one monster formula that does it all. Jun 19, 2022 at 16:09
• Wow thank you for the formula~ But I do agree with you, that one is definitely long, and at this point of my knowledge I also think I find the 3 separate formulas more understandable. Thank you so much again. I didn't think it would end up to be a monster formula :D Jun 19, 2022 at 17:07

Use this monster:

``````=arrayformula(
query(
iferror(
value(
trim(
regexextract(
substitute(
transpose(
split(
query(
filter(
substitute( substitute(Data!C2:C, " ", "_"), char(10), " " ),
(Data!B2:B = "paid") + (Data!B2:B = "not paid")
),
"", 9^9
),
" "
)
),
"_", " "
),
"^(\d+)\s(.*)\$"
)
)
),
trim(
regexextract(
substitute(
transpose(
split(
query(
filter(
substitute( substitute(Data!C2:C, " ", "_"), char(10), " " ),
(Data!B2:B = "paid") + (Data!B2:B = "not paid")
),
"", 9^9
),
" "
)
),
"_", " "
),
"^(\d+)\s(.*)\$"
)
)
),
"select Col2, sum(Col1)
group by Col2",
0
)
)
``````