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. Press with left mouse button on "Conditional Formatting" button on the ribbon.
  4. Press with left mouse button on "New Rule...", a dialog box appears. See the image above.
  5. Type your formula in "Format values where this formula is true:".
  6. Press with left mouse button on "Format..." button and another dialog box appears.
  7. Press with left mouse button on tab "Fill" on the top menu.
  8. Pick a color.
  9. Press with left mouse button on "OK" button.
  10. Press with left mouse button on "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. Press with left mouse button on the checkbox accordingly based on the layout of your data set.
  4. Press with left mouse button on "OK" button to create the Excel Table.
  5. Press with left mouse button on 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, press with left mouse button on "Filter by Color". Another pop-up menu appears, press with left mouse button on 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", press with left mouse button on that tab to select it.
  9. Press with left mouse button on 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. Press with left mouse button on the number next to total. An arrow appears next to the number, press with left mouse button on that arrow. See image above.
  11. Press with left mouse button on "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. Press with right mouse button on on a cell that has a background color you want to count. A pop-up menu appears.
  2. Press with left mouse button on "Sort" and another pop-up menu shows up.
  3. Press with mouse 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 press with left mouse button on "Filter" button to apply it.
  3. Press with left mouse button on 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. Press with mouse on "Filter by Color" and another pop-up menu appears.
  5. Press with mouse 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, ...)

Get the Excel file


Count-cells-with-Conditional-formatting.xlsx

Back to top

Recommended reading