Help with an Excel formula

[Seaweedfarm]

Hi all, 

I was wondering if anyone can solve this problem please. I am trying to get a formula that will add something like the following cells:

The formula needs to add the numbers depending on the cell colour whilst ignoring the numbers in brackets. The answer would be 10 for the total cells in orange. The number in brackets (they are dates) in the cell is not constant. The formula also must not delete the dates as they are needed visually. 

Any ideas?

Thanks,

Ariana

[#2]

@Seaweedfarm 

=SUM(NUMBERVALUE(RIGHT(A1:A3,LEN(A1:A3)-FIND(")",A1:A3))))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

[#3]

Hello @OliverScheurich. Thank you for your response. I have tried the formula but it does not seem to work.

[#4]

@Seaweedfarm 

Have the colours been applied manually or are they the result of conditional formatting?

[#5]

@Seaweedfarm 

Assuming that the colours have been applied directly, you'll need a custom VBA function:

Function SumByColor(DataRange As Range, ColorCell As Range) As Double
    Dim Cell As Range
    Dim Parts() As String
    Application.Volatile
    For Each Cell In DataRange
        If Cell.Interior.Color = ColorCell.Interior.Color Then
            Parts = Split(Cell.Value)
            On Error Resume Next
            SumByColor = SumByColor + Parts(1)
            On Error GoTo 0
        End If
    Next Cell
End Function

Use as in the screenshot:

The attached demo workbook is a macro-enabled workbook, so make sure that you allow macros when you open it.

[#6]

Thank you @Hans Vogelaar. That has worked perfectly!