What a time to be alive: soon enough we are going to see even electric kettles with GTP-4 capabilities and yet, we still don’t have a proper way to count cells based on colour in Excel.

It’s not a big deal, just a few lines in VBA that, since it’s not the first time I’m needing, I’ll just paste here:

Function COUNTIFYELLOW(CountRange As Range) 'CountRange represents the cell range you are applying this to
Dim yellow As Integer
Dim TotalCount As Integer
yellow = 6 '6 is the color index number of the particular yellow I was using, hardcoding is not cool, I know

Set rCell = CountRange 'for every cell of the select range, increment 1 if yellow
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = yellow Then
    TotalCount = TotalCount + 1 
  End If

Next rCell
COUNTIFYELLOW = TotalCount
End Function
Copy

But what if you need to count cells based on another colour? How would you know the colour index? With another dumb yet fairly useful custom function, of course:

Function GETCOLORINDEX(cell As Range)
GETCOLORINDEX = cell.Interior.ColorIndex
End Function
Copy

Just select the coloured cell you need to pick the colour index from, and you’ll get the corresponding integer.

Now, yes, hardcoding values is bad practice. Even in Excel, I guess. So to make the colour index dynamic, you can add another argument to the first function:

Function COUNTIFYELLOW(CountRange As Range, ColourRange As Range)
Dim colour As Integer
colour = ColourRange.Interior.ColorIndex 'get the colour from the cell in the second argument
Dim TotalCount As Integer

Set rCell = CountRange 'for every cell of the select range, increment 1 if the colour index matches
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = colour Then
    TotalCount = TotalCount + 1 
  End If

Next rCell
COUNTIFYELLOW = TotalCount
End Function
Copy

With the second argument (ColourRange), point to the cell with the colour you are interested into getting and you are ready to go.

Now, what if you need to manage more than one colour and more than one increment type? Say, add 1 if “yellow” and 0,5 if “blu”

This should do:

Function COUNTIFYELLOW(CountRange As Range)
Dim yellow As Integer
Dim blu As Double 'to have decimals, we are going to need this to be a Double
Dim TotalCount As Double

yellow = 6 'yeah, still hardcoding indexes here
blu = 23 '

Set rCell = CountRange
For Each rCell In CountRange 'adds 1 if yellow and 0,5 if blu
  If rCell.Interior.ColorIndex = yellow Then
    TotalCount = TotalCount + 1
  ElseIf rCell.Interior.ColorIndex = blu Then
    TotalCount = TotalCount + 0.5 
  End If
  
Next rCell
COUNTIFYELLOW = TotalCount
End Function
Copy