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

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:

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.

Download Practice Workbook

To practice by yourself, download the following 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.

<< Go Back to Colored Cells | Count Cells | Formula List | Learn Excel