Sum cells based on colour and date

Question

I have the following:

Date ------- Cost

Jan      £500
Jan      £600
Feb      £300
Feb      £600
March    £1000
March    £500

The cost cells are coloured differently depending on its current status (confirmed green, unconfirmed white, semi-confirmed yellow), I need a formula to sum all costs that are for example, green and in February.

I'm aware VBA is required for this sort of colour function, and have found a useful one called colorfunction() that allows me to sum/count cells of colours using the following formula:

colorfunction(A1, B1:B5, FALSE)

A1 being the colour to compare the range against, and FALSE / TRUE returning the sum or count result.

However I cannot work this custom function into a MONTH() formula or SUMIF. I could be completely over-complicating this so please point out any idiotic mistakes I'm making in trying to figure this out.

user2140261 user2140261 · Accepted Answer · 2013-05-09T16:11:37

Add this Function to your VBA module in order to return a cells interior color index:

    Function ColorIndex(rng As Range, _
                    Optional text As Boolean = False) As Variant
    Dim cell As Range, row As Range
    Dim i As Long, j As Long
    Dim iWhite As Long, iBlack As Long
    Dim aryColours As Variant

    If rng.Areas.Count > 1 Then
        ColorIndex = CVErr(xlErrValue)
        Exit Function
    End If

    iWhite = WhiteColorindex(rng.Worksheet.Parent)
    iBlack = BlackColorindex(rng.Worksheet.Parent)

    If rng.Cells.Count = 1 Then
        If text Then
            aryColours = DecodeColorIndex(rng, True, iBlack)
        Else
            aryColours = DecodeColorIndex(rng, False, iWhite)
        End If

    Else
        aryColours = rng.Value
        i = 0

        For Each row In rng.Rows
            i = i + 1
            j = 0

            For Each cell In row.Cells
                j = j + 1

                If text Then
                    aryColours(i, j) = _
                      DecodeColorIndex(cell, True, iBlack)
                Else
                    aryColours(i, j) = _
                      DecodeColorIndex(cell, False, iWhite)
                End If

            Next cell

        Next row

    End If

    ColorIndex = aryColours

End Function


Private Function WhiteColorindex(oWB As Workbook)

Dim iPalette As Long
    WhiteColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &HFFFFFF Then
            WhiteColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
    BlackColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &H0 Then
            BlackColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, _
                                  text As Boolean, _
                                  idx As Long)
Dim iColor As Long
    If text Then
        iColor = rng.Font.ColorIndex
    Else
        iColor = rng.Interior.ColorIndex
    End If
    If iColor < 0 Then
        iColor = idx
    End If
    DecodeColorIndex = iColor
End Function

Then to get the count of all cells with the color index of 14 (Green) use sumproduct as Follows:

=SUMPRODUCT(--(ColorIndex(B1:B100000)=14),B1:B100000)

This will return the sum of all cells in Range B1:B100000 with a Color of 14 (Green)

The Final example should look something like this:

ColorIndex

Additionally if you prefer Sumifs over Sumproduct yopu have the option of using a helper Column. In the Column next to the Cost enter =ColorIndex(B1) and then drag down

enter image description here

Then in another cells Enter the Formula

=SUM(SUMIFS(B1:B10,C1:C10,14,A1:A10,{"FEB","MARCH"}))

Replacing the Months with the Months you'd like to sum (your oringal Title indicates this is your End goal).

This will sum the Cost values where the helper Row says the index is 14(Green) AND the month is either Feb or March

enter image description here

Sum cells based on colour and date

2 Answers