Combine an array formula with query function

Question

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.

Answer 1

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