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

Sum numbers within brackets only with Defined Function

Sum numbers within brackets only with Kutools for 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:

Tip: in the formula, A1 is the cell you want to extract the numbers within brackets from.

2. Select a cell which you will place the summing result at, enter =SUM(B1:B8), and press Enter key. See screenshot:

Tip: in the formula, B1:B8 is the numbers you extracted from original list of text string and want to sum up.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!

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.

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:

Note: If there are other extracting rules in the Extract List section, please uncheck these extracting rules, and only check the new added extract rule of (*).

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:

Tip. If you want to have a free trial of the Extract Text and Sum absolute values function, please go tofree download Kutools for Excel first, and then go to apply the operation according above steps.

Sum Numbers Within Brackets