excell logical functions based on cell fill color

Copper Contributor

I would like to use the countif function with a criteria based on the cell's fill color.

2 Replies

@ernljohn 

If the colors have been applied by conditional formatting, you should use the same criteria as those in the conditional formatting rule(s).

If the colors have been applied directly, you'll need a custom VBA function. This will work in the desktop version of Excel for Windows and Mac, not in the browser nor on other platforms.

And users will have to allow macros.

Also, the formulas will not update their result automatically when you apply a different color to cells in the specified range. You'll have to do something that recalculates the sheet, for example pressing F9.

Here is such a function:

 

Function CountByColor(Range2Count As Range, ColorCell As Range) As Long
    Dim Cell As Range
    Application.Volatile
    For Each Cell In Range2Count
        If Cell.Interior.Color = ColorCell.Interior.Color Then
            CountByColor = CountByColor + 1
        End If
    Next Cell
End Function

 

Example:

HansVogelaar_0-1719081357717.png

The formula in I2 is

=CountByColor($B$2:$F$11, H2)

@HansVogelaar Thank you!  I was afraid there was no preprogramed function in Excell.  I will try your suggestion with the VBA defined function.  This will, of course, change the simple spreadsheet to one allowing macros.  But it will accomplish the task.