use sumif function when criteria is cell hihlighted with a color highlight

Copper Contributor

I want to use sumif function to add a column of numbers when criteria is a fill color in the cells.  How do I do this.

9 Replies
If you have a criteria in colouring the cells, use it as a criteria in your SumIfs() fn, else you'll have to create a user defined function with VBA to achieve that.
Thanks for your reply and input. I do not have a Criteria for Cell Coloring (don't know how to do that), and I haveE no idea what VBA is or how to use t. I have used Excel for over 35 years (in Engineering, Finance, & Ocean Navigation), but I am not a Guru in it. Thanks for any additional input.


Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Function CountByColor(ARange As Range, ColorCell As Range) As Long
    Dim ACell As Range
    For Each ACell In ARange
        If ACell.Interior.Color = ColorCell.Interior.Color Then
            CountByColor = CountByColor + 1
        End If
    Next ACell
End Function

Switch back to Excel.

Use the function like this:


In the formula =CountByColor($A$1:$A$19,D1), the first argument $A$1:$A$19 is a range with colored cells, and the second argument D1 is a cell with the color that you want to count.

Save the workbook as a macro-enabled workbook (*.xlsm), and make sure that you allow macros when you open it.

Warning: a formula like this will not automatically update its result when you change the fill color of a cell. It will be updated the next time Excel recalculates formulas; you can force this by pressing F9.


I have attached a sample workbook with the code.

@Hans Vogelaar  How do use sumifs with cell color as part of the criteria?


Do you want to impose both a color condition AND other criteria? That could become complicated.

Can you provide an example of what you want to do?


Thanks. See the attached demo workbook. The function used is

Function SumIfColour(SumRange As Range, CriteriaRange As Range, Criteria As Variant, _
        ColourRange As Range, ColourCell As Range) As Double
    Dim i As Long
    Dim r As Double
    For i = 1 To SumRange.Count
        If CriteriaRange(i).Value = Criteria And ColourRange(i).Interior.Color = _
                ColourCell.Interior.Color Then
            r = r + Val(SumRange(i).Value)
        End If
    Next i
    SumIfColour = r
End Function

One more, How can i use SumifsColour(


Once again, please provide an example of what you want to do.