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

New 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.

3 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.