sumif criteria based on cell color

[sachin483]

i want to use sum-if criteria based on cell color, can it be done with excel formula or macro

[JKwan]

here is what I found on Chip Pearson's web site

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(SumRange.Cells(N).Value) = True Then
                        D = D + SumRange.Cells(N).Value
                    End If
                End If
            Else
                If .Interior.ColorIndex = CI Then
                    If IsNumeric(SumRange.Cells(N).Value) = True Then
                        D = D + SumRange.Cells(N).Value
                    End If
                End If
            End If
        End With
    Next N
    
    SumColor = D
End Function
Private Function IsValidColorIndex(ColorIndex As Long) As Boolean
    Select Case ColorIndex
        Case 1 To 56
            IsValidColorIndex = True
        Case xlColorIndexAutomatic, xlColorIndexNone
            IsValidColorIndex = True
        Case Else
            IsValidColorIndex = False
    End Select
End Function

[sachin483]

thanks for the function , can you help me out with this function in my file

[JKwan]

here ya go