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.
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
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:
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:
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: