Count and Sum by Background Color, with color set by both Manual and conditional formatting

Hr_Justino 

In VBA, you can use range.DisplayFormat.Interior.ColorIndex to return the color of a cell as displayed, whether through direct formatting or through conditional formatting. But unfortunately, DisplayFormat does not work when used directly in a user-defined function, so you have to use a trick.

Create a function like this:

Function DColorIndex(r As Range) As Long
    DColorIndex = r.DisplayFormat.Interior.ColorIndex
End Function

You can then use these functions:

Function ColourCount(cel As Range, ran As Range) As Long
    Dim colo As Long
    Dim c As Range
    Dim cou As Long
    colo = cel.Interior.ColorIndex
    For Each c In ran
        If ran.Parent.Evaluate("DColorIndex(" & c.Address & ")") = colo Then
            cou = cou + 1
        End If
    Next c
    ColourCount = cou
End Function

Function ColourSum(cel As Range, ran As Range) As Double
    Dim colo As Long
    Dim c As Range
    Dim colsum As Double
    colo = cel.Interior.ColorIndex
    For Each c In ran
        If ran.Parent.Evaluate("DColorIndex(" & c.Address & ")") = colo Then
            colsum = colsum + c.Value
        End If
    Next c
    ColourSum = colsum
End Function

By the way 1: Excel supports many more colors than just the 56 palette colors. It might be better to use the Color property instead of the ColorIndex property.

By the way 2: in the future, please post the code instead of a screenshot of the code! That saves the person trying to help you a lot of time.