# Count Cells by Color with Conditional Formatting in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

We may sometimes need to count the Excel cells by Color. That is not hard to do. But, things get complicated when we try to count the cells by color with Conditional Formatting. So, this article will show you the effective methods to Count Cells by Color with Conditional Formatting in Excel.

Dataset Introduction

To illustrate, Iâ€™m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company. We are going to apply Conditional Formatting in this dataset.

The Conditional Formatting feature in Excel is used to format the font color, border, etc. of a cell based on a certain condition. In this case, see the steps below to color the sales in Light Red where it exceeds \$10,000.

STEPS:

• First, select the range of cells to work with.

• Then, select Greater Than from Highlight Cell Rules options in the Conditional Formatting drop-down list under the HomeÂ tab.

• A dialog box will pop out. Here, type 10000 in the Format cells that are GREATER THAN box and select Light Red Fill in the withÂ section.
• After that, press OK.

• As a result, youâ€™ll see the sales which exceed \$10,000 in light red color as it is shown in the following picture.

## 1. Applying Excel Filter to Count Cells by Color with Conditional Formatting

We know Excel provides various Features and we use them for many purposes. Such a kind is the Filter feature which filters the cell values based on different criteria. In our first method, weâ€™ll use the Filter feature to gather the colored cells and also to filter out other cells. Moreover, weâ€™ll use the SUBTOTAL functionÂ to count those colored cells. The SUBTOTAL function performs various tasks depending on the function number in the argument. So, follow the steps given below to perform the task.

STEPS:

• Firstly, select cell D4.

• Next, under the Home tab and in the Editing group, select Filter from the â€˜Sort & Filterâ€™ drop-down.

• Then, select the drop-down symbol beside the header Net Sales.
• Subsequently, select the light red color from the Filter by Cell Color options as itâ€™s shown below.

• After that, select cell D12 and type the formula:
`=SUBTOTAL(2,D6:D8)`

Here, 2 is the function number for counting and D6:D8 is the range.

• Finally, press Enter and youâ€™ll get the desired count result.

## 2. Counting Colored Cells with Conditional Formatting with Excel Table

Another useful feature in Excel is the Table feature. We insert tables in our datasheet to perform different kinds of tasks. Therefore, go along the process to know how to apply the Table feature to count colored cells with conditional formatting.

STEPS:

• In the beginning, select the range.

• Then, under the Insert tab, select Table.

• A dialog box will pop up and there, click the My table has headersÂ box.
• After that, press OK.

• Subsequently, click the drop-down symbol beside the header Net Sales.
• And then, select the light red color from the Filter by Cell Color options in the Filter by ColorÂ list.

• Consequently, itâ€™ll return the table with the selected cell color only.
• Now, select any cell inside the table and so, youâ€™ll see a new tab named Table Design.
• Then, check the Total Row box which youâ€™ll find in the Table Style Options list under the Table DesignÂ tab.

• As a result, youâ€™ll see a new row just under the table and the sum of the sales in cell D11.

• Next, click the drop-down symbol in cell D11 and select Count from the list.

• Eventually, cell D11 will show the colored cell count.

## 3. Using Excel Sort Tool to Count Conditionally Formatted Colored Cells

Lastly, weâ€™ll use the Excel Sort feature to count conditionally formatted colored cells. Hence, follow along with the process to perform the task.

STEPS:

• First, select any colored cell you want to work with. In this case, select cell D6.

• Then, right-click on the mouse and select Put Selected Cell Color On Top from the SortÂ option.

• Thus, youâ€™ll see the colored cells at the top.
• Afterward, select the colored cells as shown below.

• Finally, youâ€™ll see the count of the colored cells at the bottom-right side of the workbook.

## Conclusion

Henceforth, you will be able to Count Cells by Color with Conditional Formatting in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Donâ€™t forget to drop comments, suggestions, or queries if you have any in the comment section below.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and Iâ€™ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

1. So no formulae to keep a running count of items that have broken the conditional formatting threshold preset?
It’s easy to check the count on hand, but I need to write a formulae so another table updates with the status of the conditional formatting thresholds.

Mohammad Shah Miran Mar 9, 2023 at 3:19 PM

Thank you VIRGIAL for your query. Yes, you can use the COUNTIF function to count the number of items that have broken the Conditional Formatting threshold value. For doing this, write down the following formula in your desired cell. (eg. D11)
`=COUNTIF(D5:D10, "<12000")`
Here, D5:D10 is the data range for which you want to set your condition and 12000 is the Conditional Formatting threshold value.