As of Excel 2019, there are no built-in formulas to count cells based on background color (also known as fill color or interior color). The following user-defined functions (UDF) provide the capability to count all cells in a range with a particular background color.
Why would anyone need to count cells based on background color? There are usually better values or criteria available in the data set itself to count, right? Since you’ve found yourself reading this post, you must have a specific reason. For me, counting background color was needed to create an error checking dashboard. On a rather complicated Excel workbook, there were a number of different error checks throughout the workbook that would trigger conditional formatting when specific error conditions were met. Instead of creating additional helper columns to flag the error conditions, the conditional formatting rules turned a cell’s background color red if any error conditions were met. The dashboard view then used the COUNT_BACKGROUND_COLOR function to count all of these flagged errors (the red cells) and summarize the errors on an easy to monitor dashboard so that the errors could be corrected.
The two user-defined functions demonstrated in this post are GET_BACKGROUND_COLOR and COUNT_BACKGROUND_COLOR.
Usage
GET_BACKGROUND_COLOR(reference)
The GET_BACKGROUND_COLOR function evaluates a single cell and returns it’s color index. The color index is a numeric value corresponding to a particular color recognized by Excel.
| Argument | Required | Description |
|---|---|---|
| reference | Yes | Reference to a single cell. |
COUNT_BACKGROUND_COLOR(color index, range1, [range2], …)
The COUNT_BACKGROUND_COLOR function counts all cells in a specified range where interior color is set to the supplied color index.
| Argument | Required | Description |
|---|---|---|
| color index | Yes | Numeric value corresponding to a color; use xlNone for no color. |
| range1 | Yes | Reference to a single cell or range of cells to evaluate. |
| [range2, …] | No | Additional references to a single cell or range of cells to include in the evaluation. |
Known Limitations
If a large, used range is supplied to the COUNT_BACKGROUND_COLOR function, then Excel may become unresponsive. The function counts cells by evaluating each cell individually for the supplied color index. As an example, passing whole column references in a large data set may require evaluating 1,048,576 cells for each whole column. If whole column references are supplied to the function but the worksheet used range is mostly empty, then the function will ignore the empty cells and perform as expected.
Creating the Functions
Step 1 – Open Visual Basic
From the Developer ribbon in Excel, click Visual Basic.
The Microsoft Visual Basic for Applications window opens.
Step 2 – Insert New Module
From the Insert menu, click Module.
Step 3 – Add Code
Add the below code to the newly created module.
Step 4 – Close Visual Basic
Close the Microsoft Visual Basic for Applications window.
Result
After adding red, yellow, or green fill colors to a few cells on an Excel worksheet, we can test the two new functions. In these examples, the colors are applied to cells in the E2:G16 range.
Example 1 – Red Background Color
The GET_BACKGROUND_COLOR function returns a value 3 which corresponds to the red fill color used on cell E3.
=GET_BACKGROUND_COLOR(E3)The COUNT_BACKGROUND_COLOR function returns a value 13. Manually counting all red cells in the E2:G16 range confirms that there are 13 red filled cells.
=COUNT_BACKGROUND_COLOR(3,E2:G16)Example 2 – Yellow Background Color
The GET_BACKGROUND_COLOR function returns a value 6 which corresponds to the yellow fill color used on cell E6.
=GET_BACKGROUND_COLOR(E6)The COUNT_BACKGROUND_COLOR function returns a value 5. Manually counting all yellow cells in the E2:G16 range confirms that there are 5 yellow filled cells.
=COUNT_BACKGROUND_COLOR(6,E2:G16)Example 3 – Green Background Color
The GET_BACKGROUND_COLOR function returns a value 43 which corresponds to the green fill color used on cell G6.
=GET_BACKGROUND_COLOR(G6)The COUNT_BACKGROUND_COLOR function returns a value 6. Manually counting all green cells in the E2:G16 range confirms that there are 6 green filled cells.
=COUNT_BACKGROUND_COLOR(43,E2:G16)
Source Code
Option Explicit
Function GET_BACKGROUND_COLOR(rCell As Range) As Long
If IsObject(rCell) Then
If rCell.Cells.Count = 1 Then
GET_BACKGROUND_COLOR = rCell.Interior.ColorIndex
Exit Function
End If
End If
GET_BACKGROUND_COLOR = CVErr(xlErrRef)
End Function
Function COUNT_BACKGROUND_COLOR(lBackgroundColor As Long, ParamArray data()) As Long
Dim lParamArrayIterator As Long
Dim vCellIterator As Variant
Dim lCellCount As Long: lCellCount = 0
For lParamArrayIterator = LBound(data) To UBound(data)
If IsObject(data(lParamArrayIterator)) Then
Set data(lParamArrayIterator) = Intersect(data(lParamArrayIterator), data(lParamArrayIterator).Parent.UsedRange)
For Each vCellIterator In data(lParamArrayIterator)
If vCellIterator.Interior.ColorIndex = lBackgroundColor Then
lCellCount = lCellCount + 1
End If
Next vCellIterator
End If
Next lParamArrayIterator
COUNT_BACKGROUND_COLOR = lCellCount
End Function
