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

Count cells based on Conditional formatting

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range B2:C11, column C has Conditional Formatting applied based on the following CF formula.

 

=C3>25

A Conditional Formatting formula allows you to create your own condition or criteria if the built-in conditions are not enough.

As far as I know, you can only count cells with cell background color, not font color or bold/italic etc. It is also not possible to count cells highlighted with Conditional Formatting using VBA code, however, you can count cells manually formatted using VBA code.

If you know how to apply Conditional Formatting based on a formula you can skip these steps and go to the next section right now. Here are the steps to apply Conditional Formatting to a cell range.

How to apply Conditional Formatting

Apply Conditional formatting to a cell range

These steps explain how to apply Conditional Formatting to a cell range based on a formula.

  1. Select the cell range you want to highlight.
  2. Go to tab "Home" on the ribbon.
  3. Click "Conditional Formatting" button on the ribbon.
  4. Click "New Rule...", a dialog box appears. See the image above.
  5. Type your formula in "Format values where this formula is true:".
  6. Click "Format..." button and another dialog box appears.
  7. Click tab "Fill" on the top menu.
  8. Pick a color.
  9. Click "OK" button.
  10. Click "OK" button again.

Back to top

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

Count cells based on Conditional formatting using an Excel Table2

These steps show how to count highlighted cells.

  1. Select any cell in the data set.
  2. Press CTRL + T to open "Create Table" dialog box.
    Create Excel Table count cells
  3. Click the checkbox accordingly based on the layout of your data set.
  4. Click "OK" button to create the Excel Table.
  5. Click the arrow next to the column name you want count cells in.
    Count Conditional formatted cells in an Excel Table
  6. A pop-up menu appears, click "Filter by Color". Another pop-up menu appears, click the color you want to sort by.
    Count cells based on Conditional formatting using an Excel Table3
  7. The Excel Table now shows only the cells with the selected background color.
  8. Select any cell in the Excel Table and a new tab on the ribbon shows up named "Table Design", click that tab to select it.
  9. Click the checkbox "Total Row" located on the ribbon, see image above. A new row appears below the Excel Table values.
    Count cells based on Conditional formatting using an Excel Table1
  10. Click the number next to total. An arrow appears next to the number, click that arrow. See image above.
  11. Click "Count".

Count cells based on Conditional formatting using an Excel Table2

The number of cells highlighted with a given cell background color using conditional formatting is shown in cell C12.

Back to top

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

  1. Right-click on a cell that has a background color you want to count. A pop-up menu appears.
  2. Click "Sort" and another pop-up menu shows up.
  3. Click on "Put Selected Cell Color On Top".
    Count cells based on Conditional formatting previous excel versions3
  4. Select all colored cells.
    Count cells based on Conditional formatting previous excel versions4
  5. Excel returns the count of your selection in the lower right corner of your Excel window. See image above.

Back to top

Count cells with a specific cell background color using the FILTER tool and the SUBTOTAL function [Excel 365]

Count cells using Excels Filter tool

This section demonstrates how to count cell background color based on Conditional Formatting using the Filter tool. The Filter tool is built-in to Excel, the following steps show you how.

  1. Select any cell in your data set.
  2. Press shortcut keys CTRL + SHIFT + L to apply the Filter feature to your data set. You know it is there when you have arrows next to your column names. You can also go to tab "Data" on the ribbon and click on "Filter" button to apply it.
  3. Click the arrow in the column you want to count a specific cell background color. A pop-up menu appears.
    Count cells using Excels Filter tool1
  4. Click on "Filter by Color" and another pop-up menu appears.
  5. Click on the color you want to filter by, see image above.

The following formula will count visible cells in cell range C3:C11 that is not empty. I entered it in cell C13.

=SUBTOTAL(103,C3:C11)

The SUBTOTAL function allows you to perform many different calculations based on the first argument. It is also able to perform these calculations to filtered values in contrast to the regular SUM, AVERAGE, COUNT and COUNTA functions.

SUBTOTAL(function_num, ref1, ...)

Back to top

Recommended reading