Counting Based on Background Color in Excel

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.

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.

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