You can count cells based on color using a custom formula in Excel. In the example, we’ll show you how to do that!
Counting colored cells in Excel is difficult, as the default functions cannot access the Excel Object Model, which handles color management. Fortunately, we can quickly write a user-defined function that makes this task manageable.
How to count cells based on cell color
To count colored cells in Excel, follow these steps:
- Type =COUNTBYCOLOR(B2, E5:E10)
- Add the cell (B2) that contains the color you want to count.
- Select the range (E5:E10) where you want to count the cells with that color.
- Press Enter to see the result.
Example
The main goal of the COUNTBYCOLOR function is to count cells in a range that matches a specific color. It requires only two arguments. The first is the cell with the color to be matched. The second is the range to check. To make it clear, here’s a practical example.
In the example, the formula =COUNTBYCOLOR(B4, D2:D11) is used to count the number of cells in the range D2:D11 that have the same color as cell B4. The result is 3, indicating three cells with the same color as B4 in the specified range.
COUNTBYCOLOR Function: Under the Hood
In this section, we publish the function’s source code. We have added comments to make it easier to understand. For those interested in how the function works, this is likely the most effective way to help.
Function COUNTBYCOLOR(refColor As Range, ParamArray cellRanges() As Variant) As Long Dim currentCell As Range ' Variable to iterate through each cell in the target range Dim singleRange As Variant ' Variable to hold each range in the ParamArray Dim totalMatches As Long ' Variable to store the total count of matching cells Dim matchCriteria As Integer ' Variable to determine the type of cells to count (all, non-blank, blank) totalMatches = 0 ' Initialize the total matches count to 0 matchCriteria = 0 ' Default to counting all cells ' Check if the last argument in the ParamArray is a number, which would set the match criteria If UBound(cellRanges) > 0 Then If IsNumeric(cellRanges(UBound(cellRanges))) Then matchCriteria = cellRanges(UBound(cellRanges)) End If End If ' Loop through each range provided in the ParamArray For Each singleRange In cellRanges ' Check if the current item in ParamArray is a Range If TypeName(singleRange) = "Range" Then ' Loop through each cell in the current range For Each currentCell In singleRange ' Check if the cell color matches the reference color If currentCell.Interior.color = refColor.Interior.color Then ' Decide what to count based on the match criteria Select Case matchCriteria Case 0 ' Count all cells (default) totalMatches = totalMatches + 1 Case 1 ' Count only non-blank cells If Not IsEmpty(currentCell.Value) Then totalMatches = totalMatches + 1 End If Case 2 ' Count only blank cells If IsEmpty(currentCell.Value) Then totalMatches = totalMatches + 1 End If End Select End If Next currentCell ' If the item is an array of ranges (from a multi-area range), loop through each sub-range ElseIf TypeName(singleRange) = "Variant()" Then Dim subRange As Range ' Variable to hold each sub-range within a multi-area range For Each subRange In singleRange ' Loop through each cell in the sub-range If TypeName(subRange) = "Range" Then For Each currentCell In subRange ' Check if the cell color matches the reference color If currentCell.Interior.color = refColor.Interior.color Then ' Decide what to count based on the match criteria Select Case matchCriteria Case 0 ' Count all cells (default) totalMatches = totalMatches + 1 Case 1 ' Count only non-blank cells If Not IsEmpty(currentCell.Value) Then totalMatches = totalMatches + 1 End If Case 2 ' Count only blank cells If IsEmpty(currentCell.Value) Then totalMatches = totalMatches + 1 End If End Select End If Next currentCell End If Next subRange End If Next singleRange COUNTBYCOLOR = totalMatches ' Return the total count of matching cellsEnd FunctionYou can download the sample file here if you do not want to install our add-in.
Final words
It is evident that in many cases, despite our best intentions, we can’t use built-in Excel formulas. In such situations, it’s worth writing a simple function using VBA. You can easily use the function you create in any Excel environment without compatibility issues.
