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)
SpyderGST1Asked:
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 Excel-Tips.com (http://exceltip.com/st/Sum_by_color_using_VBA_in_Microsoft_Excel/517.html)

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

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

http://www.cpearson.com/Excel/colors.aspx

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

Kyle
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
    Else
        CI = xlColorIndexAutomatic
    End If
Else
    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
    Else
        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

0
 
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

Regards,
Chris
0
All Courses

From novice to tech pro — start learning today.