Sumif function doesn't work with ColorIndex criteria

[whitehawk81]

Hi,
I just found an issue concerning the Sumif function. When I enter the ColorIndex as criteria, it gives 0 as result. I tried to enter the formula in many ways, I even setup a ColorIndex function to check if the color index of the cell in criteria is actually the same as for the cells in criteria range. Basically I have a table, where only the cells in the first column have a specific background color. I wanted to sum the other columns based on the background color of the cells in column A.
Here are some of my attempts:

=SUMPRODUCT(--(ColorIndex(DS[DSname])=ColorIndex(A45));DS[MBB])

=SUMIF(DS[DSname];ColorIndex(A71);DS[MBB])

Function SumifByColor(critRange As Range, CellColor As Range, rRange As Range)Dim cSumif As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex


For Each cl In rRange
  If critRange.Interior.ColorIndex = ColIndex Then
    cSumif = WorksheetFunction.SumIf(critRange, CellColor, rRange)
  End If
Next cl
SumifByColor = cSumif
End Function

Does anyone have an idea, how this can be done?

 

[Marcelo Branco]

Were the cells colored manually or by Conditional Formatting? If the latter tells us the rule used in CF.

M.

 

[whitehawk81]

The cells were colored manually and when I use this ColorIndex function, I get the correct index numbers for the cells.

Public Function ColorIndex(CellColor As Range)ColorIndex = CellColor.Interior.ColorIndex
End Function

 

[Marcelo Branco]

This can be helpful
http://www.exceltrick.com/how_to/sum-cells-based-on-background-color/

Or
http://www.excel-university.com/sum-by-color/

M.

 

[whitehawk81]

Thanks! Actually I got the ColorIndex function from the first link These examples sum the values actually in the colored cells, but in my case the cell values, which I want to sum, are not in colored cells.

 

[Marcelo Branco]

But of course you know how to adapt the examples to add the neighboring cells. In fact, you have already done this in the function shown in post #1

M.

 

[whitehawk81]

I just found a solution for this issue here:
https://stackoverflow.com/questions...la-based-on-background-color-of-adjacent-cell

I changed it a bit and now it works perfectly

Public Function ColorSum(CellColor As Range, myRange As Range, x As Integer) As Variant

Dim rngCell As Range
Dim total As Variant
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex


For Each rngCell In myRange.Cells


    If rngCell.Interior.ColorIndex = ColIndex Then
    total = total + rngCell.Offset(0, x).Value
    End If


Next rngCell


ColorSum = total


End Function