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.
Read More: How to Count Colored Cells In Excel Without VBA
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.
Read More: Excel Formula to Count Colored Cells in a Row
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.
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.
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.
Additionally, the following image can be useful to comprehend the task.
hello,
Is there any method wherein one can count the number of conditional formatted color cells in row, one row at a time. formula based. each cells belongs to different column with conditional format for highlighting the Top/Bottom figures in respective columns as result the specific cell in the column gets highlighted depending on figure in that cell meeting the condition.
Been searching for answers/Tips in the net since a long time.
Regards
Thank you SHASHI, for your query. I think this article might help to solve your problem.
https://www.exceldemy.com/count-colored-cells-in-excel/#3_Utilizing_GETCELL_4_Macro_and_COUNTIFS_Functions
You can try to use this method for Conditional Formatted cells also. Further, if you have any confusion or query related to it, please let us know.