Forum Discussion
Count if color
Trying to use countifcolor function and its not listed in my excel functions. Using Microsoft 365, ver 2201
What do I need to do?
4 Replies
- As already stated, there's no such function. Color is not really data, but more like metadata (in Excel, at least) - it's more for the user's benefit when viewing the worksheet and not so much for excel to use in formula logic. But, perhaps whatever logic you are using to color your cells could be used in a countif formula - assuming the logic is based on your data.
You could use vba to do it (either as an event handler, or as a user-defined function), but you'll always have to perform some action to get it to re-calculate (whether by some worksheet event such as Niko's selectionchange code, or by triggering/forcing excel to re-calculate) - changing a cell color, by itself, doesn't trigger excel to re-calculate or trigger any events. I don't know if this is possible in your version, but one approach would be to do this with VBA. Otherwise, as Mr. Baklan has already informed 🙂
Count the number of cells with a specific cell color using VBA
Here is the example for red cells.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim anzahl As Long Dim Zelle As Range anzahl = 0 For Each Zelle In Range("E1:E100") If Zelle.Interior.ColorIndex = 3 Then anzahl = anzahl + 1 End If Next Zelle Cells(6, 6).Value = anzahl End SubHope I was able to help you with this info.
I know I don't know anything (Socrates)
There is no such function out of the box.
