SumByColor is not working

I am using this formula to sum the values of the tan cells in the identified range but all I get for the value is #NAME?

=SumByColor(B2:B34, 40)
Who is Participating?
bboswellConnect With a Mentor Commented:
I don't think there is a function in Excel called SumByColor. There is how ever a function that I located at (

You would need this in the Macro section first for this function to work.
Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
    ' Application.Volatile ' this is optional
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempSum = 0
    On Error Resume Next ' ignore cells without values
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then 
            TempSum = TempSum + cl.Value
        End If
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    SumByColor = TempSum
End Function

Open in new window

kgerbChief EngineerCommented:
Hi SpyderGST1,
This is a UDF created by Chip Pearson.  It will sum values by interior color (among other things).

Let me know if you have questions on how to implement.

Function SumColor(TestRange As Range, SumRange As Range, _
    ColorIndex As Long, Optional OfText As Boolean = False) As Variant
' SumColor
' This function returns the sum of the values in SumRange where
' the corresponding cell in TestRange has a ColorIndex (of the
' Font is OfText is True, or of the Interior is OfText is omitted
' or False) equal to the specified ColorIndex. TestRange and
' SumRange may refer to the same range. An xlErrRef (#REF) error
' is returned if either TestRange or SumRange has more than one
' area or if TestRange and SumRange have differing number of
' either rows or columns. An xlErrValue (#VALUE) error is
' returned if ColorIndex is not a valid ColorIndex value.
' If ColorIndex is 0, xlColorIndexNone is used if OfText is
' False or xlColorIndexAutomatic if OfText is True. This allows
' the caller to specify 0 for no color applied.
Dim D As Double
Dim N As Long
Dim CI As Long
Application.Volatile True
If (TestRange.Areas.Count > 1) Or _
    (SumRange.Areas.Count > 1) Or _
    (TestRange.Rows.Count <> SumRange.Rows.Count) Or _
    (TestRange.Columns.Count <> SumRange.Columns.Count) Then
    SumColor = CVErr(xlErrRef)
    Exit Function
End If
If ColorIndex = 0 Then
    If OfText = False Then
        CI = xlColorIndexNone
        CI = xlColorIndexAutomatic
    End If
    CI = ColorIndex
End If
Select Case CI
    Case 0, xlColorIndexAutomatic, xlColorIndexNone
        ' ok
    Case Else
        If IsValidColorIndex(ColorIndex:=ColorIndex) = False Then
            SumColor = CVErr(xlErrValue)
            Exit Function
        End If
End Select
For N = 1 To TestRange.Cells.Count
    With TestRange.Cells(N)
    If OfText = True Then
        If .Font.ColorIndex = CI Then
            If IsNumeric(.Value) = True Then
                D = D + .Value
            End If
        End If
        If .Interior.ColorIndex = CI Then
            If IsNumeric(.Value) = True Then
                D = D + .Value
            End If
        End If
    End If
    End With
Next N
SumColor = D
End Function

Open in new window

Chris BottomleyCommented:
Hello SpyderGST1,

The error message suggests you have the function in the wrong place .. it should reside in a normal code module i.e. NOT a class, worksheet or workbook code module

All Courses

From novice to tech pro — start learning today.