Forum Discussion

Seaweedfarm's avatar
Seaweedfarm
Copper Contributor
Sep 28, 2023

Help with an Excel formula

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

Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 28, 2023

    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.

     

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 02, 2023

        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.

Resources