Note: The other languages of the website are Google-translated. Back to English

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:
doc sum numbers in brackets 2

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:
doc sum numbers in brackets 3

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


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

doc sum numbers in brackets 4

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:
doc sum numbers in brackets 5


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:
doc sum numbers in brackets 6

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 (*).


doc sum numbers in brackets 7 shot arrow right doc sum numbers in brackets 8

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.
doc sum numbers in brackets 9

4. Select a cell which you will place the calculation result at, click Kutools > Formula Helper > Math & Trig > Sum absolute values. See screenshot:
doc sum numbers in brackets 10

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:

doc sum numbers in brackets 11 shot arrow right doc sum numbers in brackets 12

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.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (7)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, is there a Google App Script version of the VBA script. I would like to use it in Google Sheets.
Thanks. :D
This comment was minimized by the moderator on the site
Sorry I do not know
This comment was minimized by the moderator on the site
Hi, How do I modify the pattern to sum everything between F-( and )


Example of my data is: F-(10) F-(12) E-(8)


the sum would be 22


thanks
This comment was minimized by the moderator on the site
Hi, Andy, please try this code:
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
This comment was minimized by the moderator on the site
How to add numbers within the bracket?
This comment was minimized by the moderator on the site
I'm sorry, I meant adding the numbers outside the bracket.
This comment was minimized by the moderator on the site
Hi, Adduar, I do not find a formula to solve your problem, maybe you only can use the Extract Text tool of Kutools for Excel to extract the numbers first, and then sum them, the conditions are *( and )*.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations