SUM numeric values within cells that contain text & numbers

[slam]

Hi all! Hope everyone is having an amazing weekend!

I have a lot of cells that contain both text, numbers, and then a number within parenthesis that will always be at the end of the text. I want to SUM the numbers within parenthesis.

For instance, if I had the cells as per below, I am looking to have the result displayed as 60, i.e. it is only performing the SUM on cells that have a number within parenthesis (which I have bolded), and ignoring the others. Performing the calculation after Text to columns is not an option for me.

Text 1 (30)
40% Text 2
50%
200
Text 3 (10)
Text 4
+3 / 35%
Text 5 (20)

Is this possible?

Thanks!

 

[Peter_SSs]

Like this?

23 06 04.xlsm
	A	B
1	Text 1 (30)	60
2	40% Text 2
3	0.5
4	200
5	Text 3 (10)
6	Text 4
7	+3/35%
8	Text 5 (20)
Sum

Cell Formulas
Range	Formula
B1	B1	=SUM(IFERROR(-REPLACE(A1:A8,1,FIND("(",A1:A8)-1,""),0))

 

[slam]

Hi Peter,

Thank you so much for the quick reply. When I set my data up just like yours, it worked perfectly, but after reading about the REPLACE function, I'm not clear why it won't work when I update it to match my current cell references, where I want the output in B13 (then will drag across to B14, B15, etc.)

=SUM(IFERROR(-REPLACE(B3:B12,1,FIND("(",B3:B12)-1,""),0))

 

[Peter_SSs]

B13 (then will drag across to B14, B15, etc.)

Dragging across from B13 does not get you to B14, B15 etc, it gets you to C13, D13 etc.

Is this what you are trying to do? If not, please give us some dummy sample data and the expected results with XL2BB and explain again in relation to that sample data.

23 06 04.xlsm
	A	B	C	D	E	F
1
2
3		Text 1 (30)	Text 1 (30)	Text 1		A (1)
4		40% Text 2	40% Text (2)	40% Text		B (2)
5		0.5	0.5	0.5		C (3)
6		200	200	200		D (4)
7
8
9		Text 3 (10)	Text 3 (10)	Text 3		E (5)
10		Text 4	Text 4	Text 4		F (6)
11		+3/35%	+3/35%	+3/35%		G (7)
12		Text 5 (20)	Text 5 (20)	Text 5		H (8)
13		60	62	0	0	36
Sum

Cell Formulas
Range	Formula
B13:F13	B13	=SUM(IFERROR(-REPLACE(B3:B12,1,FIND("(",B3:B12)-1,""),0))

 

[slam]

Hi Peter, yes, B13, C13, D13, etc is what I meant

Your data setup is exactly what I have, but I was still not getting it to work..... until..... I was able to get it working when entering it as an array formula.

Thank you!

 

[Peter_SSs]

until..... I was able to get it working when entering it as an array formula.

OK, you must be using your 2019 version then as 365 doesn't require array entry. I made the assumption that you would be using 365

 

[slam]

Ah, understood, and thank you for explaining. I use 365 at work, and 2019 at home, and should have explained this was for 2019; my apologies, and thank you for your patience. Had not realized there could be a difference between them for this, but I will always specify in the future.

 

[Peter_SSs]

You're welcome. Glad to help.

but I will always specify in the future.

Good plan!