Sum Different Values with different Criteria-CodePudding

I am trying to SUM the Values form the Range where Single cell has almost 3 values in there i want to SUM them separatly I have tried to split the NUMBERS but unbale to SUM them.

Let me Share A picture might it can expplain better: I want the Yellow Highlighted Total Result from Above Range that is A2:A12

=ArrayFormula(REGEXREPLACE(A2:A12&"", "[^\d\.] ", "|"))

CodePudding user response：

=CONCATENATE("(",JOIN(", ",QUERY(ArrayFormula(VALUE(REGEXEXTRACT(A2:A12,"LIVE= (\d*) REVIEW= (\d*) ADDED= (\d*)"))),"select sum(Col1) sum(Col2) sum(Col3),sum(Col1),sum(Col3),sum(Col2) label sum(Col1) sum(Col2) sum(Col3) '',sum(Col1) '',sum(Col3) '',sum(Col2)'' format sum(Col1) sum(Col2) sum(Col3) '""Total=""0',sum(Col1) '""Live=""0',sum(Col3) '""Added=""0',sum(Col2) '""Review=""0'",0))," )")

CodePudding user response：

use:

="(Total= "&SUM(arrayformula(IFERROR(--regexextract(A2:A12,"= (\d )?.*?= (\d )?.*= (\d )?"))))&LAMBDA(bez,(", Live="&INDEX(bez,1)&", Added="&INDEX(bez,3)&", Review="&INDEX(bez,2)&" )"))(BYCOL(arrayformula(IFERROR(--regexextract(A2:A12,"= (\d )?.*?= (\d )?.*= (\d )?"))),LAMBDA(bx,sum(bx))))

CodePudding user response：

Try the following formula-

="Total=" & QUERY(QUERY(INDEX(SPLIT(REGEXREPLACE(A2:A7, "[=()]", "")," ")),"select sum(Col2) sum(Col4) sum(Col6) group by Col1"),"offset 1",0) & ", " & JOIN("",QUERY(QUERY(INDEX(SPLIT(REGEXREPLACE(A2:A7, "[=()]", "")," ")),"select 'LIVE=', sum(Col2),', REVIEW=',sum(Col4),', ADDED=',sum(Col6) group by Col1"),"offset 1",0))

Edit: Simplified formula-

=JOIN(", ",QUERY(
QUERY(INDEX(SPLIT(REGEXREPLACE(A2:A7, "[=()]", "")," ")),
"select sum(Col2) sum(Col4) sum(Col6),sum(Col2),sum(Col4),sum(Col6) group by Col1 
format sum(Col2) sum(Col4) sum(Col6) '""Total=""0', sum(Col2) '""LIVE=""0', sum(Col4) '""REVIEW=""0', sum(Col6) '""ADDED=""0'"),
"offset 1",0))

CodePudding user response：

Use:

="("&JOIN(", ",
      INDEX(
        {"Total","Live","Added","Review"}&"= "&
          QUERY(
            --SPLIT(A2:A12,"LIVERVWAD()="),
             "SELECT SUM(Col1) SUM(Col2) SUM(Col3), 
                     SUM(Col1),SUM(Col3),SUM(Col2)"),
         2))&
 ")"