ColorIndex formula that works with Ranges

[BMcHale]

Hi I'm using the colorindex() formula in a work book, but i would like it to work with a sumproduct() formula aswell, for instance;

Where the color index of green is 14 and yellow is 6

=SUMPRODUCT((COLORINDEX(A1:A5)=14)*(B1:B5))
Where

(Green)	15
(Green)	27
(Yellow)	62
(Green)	38
(Yellow)	47

<tbody>
</tbody>

So
=SUMPRODUCT(({14,14,6,14,6}=14)*{15,27,62,38,47})
=SUMPRODUCT({1,1,0,1,0}*{15,27,62,38,47})
=SUM(15,27,0,38,0)
=80

However when I use the range brackets, (Ctrl, Shift, Enter) it doesn't work.

Is there any way to edit the formula using VBA to allow it to work like this instance.

Thanks,
Billy

 

[Domenic]

Here's a custom function that will sum a range of cells, where the corresponding cell in another range contains the specified fill colour. First you'll need to place the following code in a regular module. So you'll need to open the Visual Basic Editor (Alt+F11), select Insert from the menu, and then select Module. Then copy the code into the code module.

Function SumByColor(rColorRange As Range, ColorIndex As Long, rSumRange As Range) As Double    
    Dim Result As Double
    Dim RowCnt As Long
    Dim ColCnt As Long
    Dim i As Long
    Dim j As Long
    
    If rColorRange.Rows.Count <> rSumRange.Rows.Count Or _
        rColorRange.Columns.Count <> rSumRange.Columns.Count Then
            SumByColor = CVErr(xlErrValue)
            Exit Function
    End If
    
    RowCnt = rColorRange.Rows.Count
    ColCnt = rColorRange.Columns.Count
    
    Result = 0
    For i = 1 To RowCnt
        For j = 1 To ColCnt
            If rColorRange(i, j).Interior.ColorIndex = ColorIndex Then
                Result = Result + rSumRange(i, j).Value
            End If
        Next j
    Next i
    
    SumByColor = Result
    
End Function

Then, close the Visual Basic Editor and return to Excel (Alt+Q). Lastly, save the workbook. Then you can use the following worksheet formula...

=SumByColor(A1:A5,14,B1:B5)

Note that the formula will not automatically re-calculate if you change the fill colour for one or more cells. You would need to either re-enter the formula or press Ctrl+Alt+F9.

Hope this helps!

 

[BMcHale]

Hi,

Thanks for your help though I feel as though I wasn't clear when I first posted. Although this works perfectly for what I queried I also need to use the Colorindex() function with other functions, for instance the Max() function and even the Sumproduct() function where a division is also required, for example;


=SUMPRODUCT(((COLORINDEX(A1:A5)=14)*(B1:B5))/(C1:C5))
=SUMPRODUCT(({14,14,6,14,6}=14)*{15,27,62,38,47})/(5,9,7,2,11))
=SUMPRODUCT(({1,1,0,1,0}*{15,27,62,38,47})/(5,9,7,2,11))
=SUMPRODUCT((15,27,0,38,0)/(5,9,7,2,11))
=SUM(5,3,0,19,0)
=27

It seems to me as though this would be solved if the Colorindex() function worked with ranges, however I don't know if it can, another example would be the Max() function;

=MAX((ColorIndex(A1:A5)=14)*(B1:B5))
=MAX(({14,14,6,14,6}=14)*(15,27,62,38,47))
=MAX({1,1,0,1,0}*(15,27,62,38,47))
=MAX(15,27,0,38,0)
=38

Thanks for your help so far.

Billy

 

[RoryA]

What COLORINDEX function are you using?

 

[Fluff]

@RoryA
Probably this one
https://www.mrexcel.com/forum/excel...n-function-based-cell-format.html#post4967124

 

[RoryA]

If so, then this should work, as long as the ranges are contiguous:

Function COLORINDEX(r As Range)
    'NB: does NOT recalculate when colour is changed.  Must press F9 to recalc manually
    Application.Volatile
    Dim x As Long, y As Long, CI As Long
    Dim out
    If r.Count > 1 Then
        ReDim out(1 To r.Rows.Count, 1 To r.Columns.Count)
        For x = 1 To r.Rows.Count
            For y = 1 To r.Columns.Count
                CI = r.Cells(x, y).Interior.COLORINDEX
                If CI = xlColorIndexAutomatic Then
                    out(x, y) = 0
                Else
                    out(x, y) = CI
                End If
            Next y
        Next x
    Else
        out = r.Interior.COLORINDEX
        If out = -4142 Then out = 0
    End If
    COLORINDEX = out
End Function

I would stress though that I think this - by which I mean using colours as data - is really bad practice.

 

[BMcHale]

Hi,

Thanks guys, this works perfectly.

Could I ask why this would be a bad practice?

The advantages I see from doing this is that it allows you to quite easily add a second piece of data to an individual cell, which in turn allows you vast amounts more functionality.

Thanks,

Billy

 

[RoryA]

Simply because changing a cell colour does not trigger a recalculation, so the chance of your numbers being wrong is quite high.

 

[Jon Peltier]

Not to mention that ColorIndex is an old old way to work with colors. You can't even apply them through the UI.

 

[BMcHale]

Ahhh that makes sense.

While this thread is still open, is there any way to do something similar to this, just using font color instead of cell color?

Thanks,

Billy