I want to know if there is a way that I can combine any of these 3 formulas.
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.