How Do I make this work??

[MaddMike]

I am trying to use this function to sum the values in one colum based upon the interrior color of another colum. In other words for all cells with a light gray interrior color in colum O add the values in colum M.

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

 

[AlphaFrog]

That function doesn't do what you want. It sums cells in one column (or range) that are all of one color.

How are your cells in column O colored? By conditional formatting or was the background interior color set manually? It matters which method.

If they were colored using conditional formatting, what is the condition that turns them gray?

 

[MaddMike]

The background color or fill is done manually.

Thanks

 

[AlphaFrog]

Try this...

[color=darkblue]Function[/color] SumIfByColor(DataRange [color=darkblue]As[/color] Range, ColorRange [color=darkblue]As[/color] Range, ColorCell [color=darkblue]As[/color] Range) [color=darkblue]As[/color] [color=darkblue]Double[/color]
    
    [color=green]' Returns the sum of each cell in the range DataRange that is in the same row as[/color]
    [color=green]' a cell in the ColorRange that is colored.[/color]
    
    [color=green]' The background color is specified by the ColorCell[/color]
    
    [color=green]' example: =SumIfByColor($M$1:$M$20, $O$1:$O$20, $B$1)[/color]
    [color=green]'    range M1:M20 is the range you want to sum[/color]
    [color=green]'    range O1:O20 is the range you want to test if colored[/color]
    [color=green]'    range B1 is a cell with the background color criteria[/color]
    
    [color=darkblue]Dim[/color] c [color=darkblue]As[/color] Range, TempSum [color=darkblue]As[/color] [color=darkblue]Double[/color], ColorNDX [color=darkblue]As[/color] [color=darkblue]Integer[/color], i As [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] ColorRange = Intersect(ColorRange.Parent.UsedRange, ColorRange)
    [color=darkblue]Set[/color] DataRange = DataRange.Resize(ColorRange.Rows.Count, ColorRange.Columns.Count)
    
    ColorNDX = ColorCell.Interior.Color[color=darkblue]In[/color]dex
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] c In ColorRange
        i = i + 1
        [color=darkblue]If[/color] c.Interior.ColorIndex = ColorNDX [color=darkblue]Then[/color]
            TempSum = TempSum + DataRange(i).Value
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] c
    
    SumIfByColor = TempSum
    
[color=darkblue]End[/color] [color=darkblue]Function[/color]

 

[MaddMike]

Thanks Alpha!!!

Can't tell you how much I appreciate you help. This works great!