excell logical functions based on cell fill color

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:

The formula in I2 is

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