How to sum numbers in brackets only in Excel?
If there is a list of text strings, and you just want to add up all numbers in brackets as below screenshot shown, how can you quickly handle it? In this article, I introduce some tricks for you to sum the numbers in brackets only in Excel.
Sum numbers within brackets only with formula
To sum numbers within brackets only, you can extract the numbers which are in brackets firstly, and then add them up.
1. Select a blank cell next to the cell you want to sum up its numbers in brackets, B2 for instance, enter this formula
=IF(ISERROR(MID(A1,FIND("(",A1)+1,(FIND(")",A1))-(FIND("(",A1)+1))),0,(MID(A1,FIND("(",A1)+1,(FIND(")",A1))-(FIND("(",A1)+1)))+0),
and then drag auto fill handle down to extract numbers which are in brackets from each cell. See screenshot:
2. Select a cell which you will place the summing result at, enter =SUM(B1:B8), and press Enter key. See screenshot:
Sum numbers within brackets only with Defined Function
You also can apply a Defined Function to sum numbers within brackets.
1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window, click Insert > Module, and paste below code to the blank script.
VBA: Sum numbers in brackets
Function SumBracket(Target As Range) As Double
'UpdatebyExtendoffice20160901
Dim xCell As Range
Dim xObjs As Object, xObj As Object
Dim xSum As Double
Set xObjs = CreateObject("VBScript.RegExp")
xSum = 0
With xObjs
.Global = True
.Pattern = "\((\d+(\.\d+)?)\)"
For Each xCell In Target
If xCell.Value <> "" Then
For Each xObj In xObjs.Execute(xCell.Value)
xSum = xSum + xObj.submatches(0)
Next
End If
Next
End With
SumBracket = xSum
End Function
2. Save the code and go to select a cell which will place the result, and enter this formula =SumBracket(A1:A8), press Enter key. See screenshot:
Sum numbers within brackets only with Kutools for Excel
Actually, with Kutools for Excel’s Extract Text function to extract numbers within brackets, and then apply the Sum absolute values function to sum the numbers up.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. |
After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)
1. Select the strings you want to extract numbers in brackets, and click Kutools > Text > Extract Text. See screenshot:
2. In the Extract Text dialog, enter (*) into the Text textbox, and click Add to add it to the Extract list. Then click Ok, and to select a cell to place the extracted numbers in the popping dialog, C1 for instance. See screenshot:
 |
 |
 |
3. Click OK, and now the numbers in brackets are extracted. In default, the numbers within brackets are recognized as negative, you need to sum their absolute values.
4. Select a cell which you will place the calculation result at, click Kutools > Formula Helper > Math & Trig > Sum absolute values. See screenshot:
5. In the Formula Helper dialog, select the range which contains the extracted numbers into Number textbox, C1:C8 in this case. Click Ok, the absolute values of the numbers have been added. See screenshot:
 |
|
 |
Sum Numbers Within Brackets
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
