PDA

View Full Version : sumif criteria based on cell color



sachin483
08-06-2015, 12:11 AM
i want to use sum-if criteria based on cell color, can it be done with excel formula or macro

JKwan
08-06-2015, 01:03 PM
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
08-06-2015, 09:10 PM
thanks for the function , can you help me out with this function in my file

JKwan
08-07-2015, 09:09 AM
here ya go