Author: Oscar Cronquist Article last updated on May 04, 2020

Count cells based on background color

This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined functions (UDF) that will count cell values based on font color, cell color and bold/italic.

How to change cell background color

Count cells based on background color fill color

Excel lets you color cells using the "Fill Color" tool located on tab "Home" on the ribbon.

  1. Select the cell or cell range you want to apply a different background color to.
  2. Go to tab "Home" on the ribbon.
  3. Click "Fill Color" button and a pop-up menu appears.
  4. Pick a color.
Tip! Double click on "Format Painter" button also available on tab "Home" on the ribbon. This copies the cell formatting of the selected cell and you can then paste the formatting to multiple cells, one by one or to an entire cell range.

Sometimes it can be useful to know the number of cells filled with a specific background color. Excel 365 makes this really easy, there is a work-around for older Excel versions that I will show in this article as well.

Back to top

How to count cells with a specific background-color [Excel 365]

Count cells based on background color Excel 365

The Excel Table allows you to filter and count cells with a specific background color.

  1. Select any cell in the data set.
  2. Press shortcut keys CTRL + T to show the "Create Table" dialog box.
    Count cells based on background color create excel table
  3. Enable the checkbox if your data has header names.
  4. Click the "OK" button to convert cell range to an Excel Table.

Count cells based on background color Filter cells by color

  1. Click the arrow next to the header name which corresponds to the column you want to filter.
  2. A pop-up menu appears, click "Filter by Color".
  3. Another pop-up menu appears, click the color you want to filter by.

Count cells based on background color Count colored cells

  1. Select any cell in the Excel Table. A new tab shows up on the ribbon named "Table Design".
  2. Click on the tab "Table Design" on the ribbon.
  3. Click on checkbox "Total Row", see image above.

A new cell appears below the Excel Table, see image above.

Count cells based on background color Total row

  1. Select the new cell. An arrow appears next to the number, see image above.
  2. Click the "Arrow", a pop-up menu appears.
  3. Click "Count".

Count cells based on background color Total count

The number in the cell changes, it now shows the total count of cells based on the filter we applied. The formula bars hows this formula:

=SUBTOTAL(103, [Numbers])

The SUBTOTAL function is able to count visible cells in a filtered table, number 103 stands for COUNT and [Numbers] is a structured reference to data in column Numbers.

Structured references appear automatically when you reference data in an Excel Table, in this case, Excel created this for us when we enabled checkbox "Total row" in a previous step.

Back to top

How to count cells with a specific background-color [Previous Excel versions]

Count cells based on background color Excel 365

  1. Right-click on a cell that has the background color you want to filter by. A pop-up menu appears.
    Count cells based on background color previous excel versions
  2. Click on "Sort", another pop-up menu shows up.
  3. Click "Put selected Cell color on top".

Count cells based on background color put selected cell color on top

The data is now sorted by cell color, see image above. Select all cells with the background color you want to count, they should now be adjacent to each other and sorted on top.

Count cells based on background color count selected cells 1

Excel counts the selected cells for and shows the number in the lower right corner of your Excel window.

Back to top

Count cells by cell font properties (font color, cell color, bold or italic)

count cells by cell formatting

The image above demonstrates a User defined function (UDF) in cell D3 that counts cells in column A based on the cell formatting in cell C3. The font properties are color, cell color and bold/italic.

Dave asks:
How difficult would it be to make it count colour alone (so not unique values) and/or use cell font colour or other features also (bold for example)?Excel has some quick green (with green text) and red (with red text) preset formats that people often use in either conditional formats or by manual application and although the unique values is very useful in a lot of cases this is even more detailed than required for just a count of the formatted conditions.Awesome site by the way, it's been invaluable to me lately!
Cheers.

Custom function in cell D3:

=CountC(A1:A20,C3)

The user defined function counts how many times the font properties in cell C3 match each cell in the cell range A1:A20.

User defined function

'Name UDF and dimension parameters and declare their data types
Function CountC(rng As Range, Cell As Range)

'Dimension variables and declare data types
Dim CellC As Range, ucoll As New Collection, i As Single

'Save 0 (zero) to variable i
i = 0

'Iterate through each cell in parameter rng using variable CellC
For Each CellC In rng
    'Check if cell background color matches parameter Cell's cell background color
    If CellC.Interior.Color = Cell.Interior.Color And _
    CellC.Font.Bold = Cell.Font.Bold And _
    CellC.Font.Italic = Cell.Font.Italic And _
    CellC.Font.ColorIndex = Cell.Font.ColorIndex Then
        'Add 1 to the number in variable i
        i = i + 1
    End If

'Continue with next cell
Next CellC

'Return number stored in variable i to UDF.
CountC = i

End Function

Back to top

Where to put the VBA code?

Count cells based on background color VB Editor

  1. Copy VBA code.
  2. Press Alt + F11 to open the VB Editor.
  3. Click "Insert" on the top menu, a pop-up menu appears. See image above.
  4. Click "Module" to insert a module to your workbook. The module appears in the "Project Explorer", in this case, named Module1.
  5. Paste code to code window.
Note, save your workbook with file extension *.xlsm (macro-enabled) workbook. This will attach the VBA code to the workbook.

Back to top

Count unique distinct cells by cell font properties (font color, cell color, bold or italic)

count unique distinct cell values by cell formatting

The image above shows a user defined function in cell D3 that counts unique distinct values in cell range A1:A20 based on the cell formatting in cell C3. This UDF also checks font color, cell color and bold/italic.

The formula in cell D3 returns 3 because the values in cell A5, A10, A16 and A17 match the cell formatting in cell C3. The values are 24, 5, 24 and 17. However, the unique distinct values are 24, 5 and 17. 3 numbers and 3 is returned to cell D3.

Custom function in cell D3:

=CountC(A1:A20,C3)

The user defined function matches the font properties in cell C3 with each cell in cell range A1:A20, then counts unique distinct values in those matching cells.

User defined function

Function CountUDC(rng As Range, Cell As Range)
Dim CellC As Range, ucoll As New Collection
For Each CellC In rng
    If CellC.Interior.Color = Cell.Interior.Color And _
    CellC.Font.Bold = Cell.Font.Bold And _
    CellC.Font.Italic = Cell.Font.Italic And _
    CellC.Font.ColorIndex = Cell.Font.ColorIndex Then
        On Error Resume Next
            If Len(CellC) > 0 Then ucoll.Add CellC, CStr(CellC)
        On Error GoTo 0
    End If
Next CellC
CountUDC = ucoll.Count
End Function

Back to top

Back to top