How to calculate the SUM with data in brackets in excel?

Question

Hi

This is a leave roster updated in this format. I need to take the sum of the numbers (inside brackets).

The list goes on i can't change the format now, appreciate if anyone can help with calculating the sum using a formula.

Use [Text to Columns](https://www.excel-easy.com/examples/text-to-columns.html) on Fixed Width to separate out the numbers from the brackets, then you can sum them normally - and use custom number formatting to show the numbers in brackets again if necessary. — Spencer Barnes, Oct 14 '20 at 06:11
You might find this existing page useful: https://superuser.com/questions/1346381/sum-the-numbers-contained-in-strings-inside-parentheses — Saint Rollox, Oct 14 '20 at 07:44
@Jason Riley This looks like it is only working for a single cell per formula. — OverflowStacker, Oct 14 '20 at 07:57
@OverflowStacker You'd Fill Down and Fill Right (12 columns, 1 for each month) then sum the values from the formula. — Saint Rollox, Oct 14 '20 at 07:59

score 1 · Answer 1 · answered Oct 14 '20 at 11:59

1

If you have access to TEXTJOIN function, you can use following array formula for any count of parentheses:

=SUM(IFERROR(FILTERXML("<a><b>" & SUBSTITUTE(SUBSTITUTE(TEXTJOIN("",TRUE,A2:L3),"(","#</b><b>"),")","</b><b>") & "</b></a>","//b"),0))

Array formula after editing is confirmed by pressing ctrl + shift + enter

answered Oct 14 '20 at 11:59

basic

11,373
2
7
25

Thought about this too, but i don't have access to `TEXTJOIN`, so substitute ist limited to one cell. What ist the meaning of `//b`, as i would have [used](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml) `//s[position() mod 2 = 1]`, meaning every odd element? – OverflowStacker Oct 14 '20 at 13:13
1

@OverflowStacker ```//b``` returns all elements as an array. In my formula, anything that is outside the parentheses is converted to text by adding a ```#``` symbol, so only the numbers in parentheses are summed. – basic Oct 14 '20 at 15:00

score 0 · Answer 2 · answered Oct 14 '20 at 08:21

A solution without helper columns and a maximum of 2 pairs of parentheses would be this ARRAY FORMULA: CTRL + SHIFT + ENTER

=SUM(
    VALUE(MID(A1:A2,SEARCH("(",A1:A2)+1,SEARCH(")",A1:A2)-SEARCH("(",A1:A2)-1)),
    IFERROR(VALUE(MID(A1:A2,SEARCH("(",A1:A2,SEARCH("(",A1:A2)+1)+1,SEARCH(")",A1:A2,SEARCH(")",A1:A2))-SEARCH("(",A1:A2)-1)),0)
    )

Adapt the ranges to your needs.

Dy.Lee · Answer 3 · 2020-10-14T17:27:43.210

If you can use vba, try using the user-defined function below.

Function mySum(rngDB As Range)
    Dim mCol As Object 'MatchCollection
    Dim Ws As Worksheet
    Dim rng As Range
    Dim strPattern As String
    Dim s As String
    Dim i As Integer, n As Integer
    Dim vSum() As Variant
    
    Application.Volatile
    Set Ws = ActiveSheet
     strPattern = "(()([0-9]{1,})())"
    For Each rng In rngDB
        s = rng.Value
        Set mCol = GetRegEx(s, strPattern)
        If Not mCol Is Nothing Then
            For i = 0 To mCol.Count - 1
                n = n + 1
                ReDim Preserve vSum(1 To n)
                vSum(n) = Val(mCol.Item(i))
            Next i
        End If
    Next
    If n Then
        mySum = WorksheetFunction.Sum(vSum)
    End If

End Function

Function GetRegEx(StrInput As String, strPattern As String) As Object
    Dim RegEx As Object 'New RegExp
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .IgnoreCase = False
        .MultiLine = True
        .Pattern = strPattern
    End With
    If RegEx.test(StrInput) Then
        Set GetRegEx = RegEx.Execute(StrInput)
    End If
End Function

How to calculate the SUM with data in brackets in excel?

3 Answers3

image