Sum Multiple Numbers in Parentheses

[bk307]

I've been searching all over for a solution to this problem. Found bits and pieces of helpful formulas but none that did exactly what I wanted.

For a cell that has multiple numbers in parentheses, is there a formula to sum them all? For example:

Cell A1
x (80), y (50), z (100)

I'd like to get a formula that would sum the numbers in A1 to get 230. There could be anywhere from 1 to 10 numbers in parentheses in each cell.

There are also cases where there are numbers not in parentheses, which I would want to ignore:

Cell A1
x (80), y x3 (50), z x2 (100)

Again, I'd like to get a sum of 230.

Is this something that is possible?

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that ..
- you have Excel 365, including the SEQUENCE function, try this
- any parentheses in the cell always contain a numerical value
.. then try this

20 07 28.xlsm
	A	B
1	x (80), y x3 (50), z x2 (100)	230
2	x(80)y	80
3	abc 80 rtgf 5	0
4	x (80), y x3 (50), z x2 (100)x (80), y x3 (50), z x2 (9865.32)x (80), y x3 (50), z x2 (100)	10455.32
5	x (-80), y x3 (50), z x2 (-100)	-130
Sum in parentheses

Cell Formulas
Range	Formula
B1:B5	B1	=SUMPRODUCT(MID(SUBSTITUTE(SUBSTITUTE(A1&REPT("(0)",10),"(",REPT(" ",100)),")",REPT(" ",100)),SEQUENCE(10,,100,200),100)+0)

 

[bk307]

I've updated to show that I have Excel 2016. Don't think the solution works in this version. Get #NAME? after I type in the formula.

 

[ngoctinh87]

I've updated to show that I have Excel 2016. Don't think the solution works in this version. Get #NAME? after I type in the formula.

Try

=SUM(IFERROR(--MID(A1,AGGREGATE(15,6,ROW($1:$50)/(MID(A1,ROW($1:$50),1)="("),ROW($1:$50))+1,AGGREGATE(15,6,ROW($1:$50)/(MID(A1,ROW($1:$50),1)=")"),ROW($1:$50))-AGGREGATE(15,6,ROW($1:$50)/(MID(A1,ROW($1:$50),1)="("),ROW($1:$50))-1),0))
Press Ctrl+Shirt+Enter

 

[Peter_SSs]

I've updated to show that I have Excel 2016.

Thanks

Don't think the solution works in this version.

That is correct (& why I wrote..)

Assuming that ..
- you have Excel 365, including the SEQUENCE function,

Try this instead. You may need to confirm the formula with Ctrl+Shift+Enter, not just Enter

20 07 28.xlsm
	A	B
1	x (80), y x3 (50), z x2 (100)	230
2	x(80)y	80
3	abc 80 rtgf 5	0
4	x (80), y x3 (50), z x2 (100)x (80), y x3 (50), z x2 (9865.32)x (80), y x3 (50), z x2 (100)	10455.32
5	x (-80), y x3 (50), z x2 (-100)	-130
Sum in parentheses

Cell Formulas
Range	Formula
B1:B5	B1	=SUMPRODUCT(MID(SUBSTITUTE(SUBSTITUTE(A1&REPT("(0)",10),"(",REPT(" ",100)),")",REPT(" ",100)),{1,3,5,7,9,11,13,15,17,19}*100,100)+0)

 

[Peter_SSs]

@ngoctinh87
I note that your formula fails for an example like I have in cell A4.
Also, if after entering your formula a user subsequently inserts any new rows at the top of the sheet, it will return incorrect results.

@bk307
My previous formula (column B below) may also fail if the overall string length is longer (eg row 6)
An alternative that is more robust is suggested in column C (still confirm with C+S+E before copying down)

20 07 28.xlsm
	A	B	C
1	x (80), y x3 (50), z x2 (100)	230	230
2	x(80)y	80	80
3	abc 80 rtgf 5	0	0
4	x (80), y x3 (50), z x2 (100)x (80), y x3 (50), z x2 (9865.32)x (80), y x3 (50), z x2 (100)	10455.32	10455.32
5	x (-80), y x3 (50), z x2 (-100)	-130	-130
6	x (80), y x3 (50), z x2 (100)x (80), yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy x3 (50), z x2 (9865.32)x (80), y x3 (50), z x2 (100)	#VALUE!	10455.32
Sum in parentheses

Cell Formulas
Range	Formula
B1:B6	B1	=SUMPRODUCT(MID(SUBSTITUTE(SUBSTITUTE(A1&REPT("(0)",10),"(",REPT(" ",100)),")",REPT(" ",100)),{1,3,5,7,9,11,13,15,17,19}*100,100)+0)
C1:C6	C1	=SUM(--REPLACE(LEFT(A1&REPT("(0)",10),FIND("#",SUBSTITUTE(A1&REPT("(0)",10),")","#",{1,2,3,4,5,6,7,8,9,10}))-1),1,FIND("#",SUBSTITUTE(A1&REPT("(0)",10),"(","#",{1,2,3,4,5,6,7,8,9,10})),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bk307]

That's awesome Peter_SSs! Thanks so much

 

[Peter_SSs]

That's awesome Peter_SSs! Thanks so much

You're welcome. Thanks for the follow-up,

If you are interested, another way would be to employ a user-defined function like this one. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down

Function SumInBrackets(s As String) As Double
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|\))([^\(]*?)(\(|$)"
  SumInBrackets = Evaluate(RX.Replace(s, "+") & 0)
End Function

bk307 2020-07-28 1.xlsm
	A	B
1	x (80), y x3 (50), z x2 (100)	230
2	x(80)y	80
3	abc 80 rtgf 5	0
4	x (80), y x3 (50), z x2 (100)x (80), y x3 (50), z x2 (9865.32)x (80), y x3 (50), z x2 (100)	10455.32
5	x (-80), y x3 (50), z x2 (-100)	-130
6	x (80), y x3 (50), z x2 (100)x (80), yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy x3 (50), z x2 (9865.32)x (80), y x3 (50), z x2 (100)	10455.32
Sum in parentheses

Cell Formulas
Range	Formula
B1:B6	B1	=SumInBrackets(A1)