# How to sum all the value inside multiple brackets within single cell in Google Sheets

I'm a complete noob to everything that concerns formulas and scripts on sheets but I thought I'd ask if a certain thing was possible:

I have no idea if this is even feasible in a formula or if I'd be better off by doing it manually but basically I'd like to do as follow:

7 (3.4) - Losjon (1.2), Grafenau (2.2)

Right now I have in a cell (left content before the '-') the total sum of numbers in between brackets in the right cell (so 1+2+2+2=7) and in between brackets in that same left cell the sum of "left" numbers (I don't know how to call them but here the numbers in italic so 1+2=3) and the sum of "right" numbers (in bold italic so 2+2=4). I wondered if this could be automatized using a formula?

• It is unclear whether you have a text string like `7 (3.4) - Losjon (1.2), Grafenau (2.2)` and want to extract numbers from it, or if you have numbers and want to build a text string like the one you quote based on those numbers. Please clarify. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Commented May 11, 2021 at 14:18

I have added a new sheet ("Erik Help") to your sample spreadsheet. There you will find the following array formula in D1 (highlighted in green):

`=ArrayFormula({"Amount - Total (Male.Female)";IF(A2:A="",,MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(E2:E,"[^0-9\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"","\d+")),SEQUENCE(10,1,1,0))+MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(REGEXREPLACE(E2:E,"\,|\)","~"),"[^0-9\~\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"~","(\d+)~")),SEQUENCE(10,1,1,0))&" ("&MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(E2:E,"[^0-9\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"","\d+")),SEQUENCE(10,1,1,0))&"."&MMULT(VALUE(REGEXEXTRACT(ARRAY_CONSTRAIN(SPLIT(REGEXREPLACE(REGEXREPLACE(E2:E,"\,|\)","~"),"[^0-9\~\.\(]","")&REPT("(0.0",10),"("),COUNTA(A2:A),10)&"~","(\d+)~")),SEQUENCE(10,1,1,0))&")")})`

This formula will produce the header and all results for Column D.

Since the formula is quite complex and designed for a unique/custom situation, it is being provided as-is without explanation.

Update: take unspecified gender cases into account per the asker's new requirements.

This may be easiest to do with a fill-down formula using regular expressions and `split()`. Try this:

``````=arrayformula(
sum( iferror( split( regexreplace(E2, "[^\d]+"," "), " ") ) )
& " (" &
sum( iferror( regexextract( trim( split( regexreplace(" " & E2 & " ", "[^\d\.]+", "π µ"), " ", false, true ) ), "(?:^µ)(\d+)" ) ) + 0 )
& "." &
sum( iferror( regexextract( trim( split( regexreplace(" " & E2 & " ", "[^\d\.]+", "π µ"), " ", false, true ) ), "(?:^µ\d+\.)(\d+)" ) ) + 0 )
&
if(
regexmatch(E2, "\d+\.\d+\.\d+"),
"." &
sum( iferror( regexextract( split( regexreplace(" " & E2 & " ", "[^\d\.]+", "π µ"), " ", false, true ), "(\d+)π" ) ) + 0 ),
""
)
& ")"
)
``````

See the new `Solution` sheet in your sample spreadsheet. The formulas are in cells `D2:D9`.

To learn the exact regular expression syntax used by Google Sheets, see RE2.

• Thank you so much both of you! This is a tremendous gain of time for me and it helps a lot, thanks a lot. Just one last question, I added another situation in the sheets (highlighted in green) where I have unspecified gender specimens (instead of males and females) and in that case of course the formula you gave, @doubleunary, doesn't work (because of course this wasn't a type ex. I gave before). Would there be any way to edit the current formula to take that into consideration if possible? Commented May 11, 2021 at 20:32
• Thank you so much once again! And indeed, this is one of my first times on this site so I'll take a look at the help page. One last thing, the code works greatly except for one tiny thing: when there is a zero (so let's say for ex. 1.0), it counts it as whatever other number there is in the case (so here, 1, which thus becomes 1.1 in the total sum case). I've put two exemples of this in the sample spreadsheet (highlighted in pink) if you have any desire or time to look at it. Commented May 11, 2021 at 21:13
• Edited the answer per your new requirements. You have accepted Erik's post as the best answer. If that is really the case, please adjust his formula to your requirements, and ask him for more help if you have trouble with his solution. Commented May 12, 2021 at 5:59