Forum Discussion
excell logical functions based on cell fill color
I would like to use the countif function with a criteria based on the cell's fill color.
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)
- ernljohnCopper Contributor
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.