Mahbubur Rahman
Coloring cells is a popular method to visualize data in an Excel worksheet. Sadly, there is no in-built function in Excel to do it. We’re going to show you some simple methods using Excel formula to count cells with a specific color. We have attached a sample workbook containing Date, Product, Country, Quantity, and Sales for your better understanding.
Download Practice Workbook
4 Ways to Count Cells with Specific Color Using Excel Formula
Our four methods include Excel commands, functions, and a VBA code.
Method 1: Filters and the SUBTOTAL Function to Count Colored Cells
We can also use the filter option to count specific cells with color in Excel. We will see the use of the SUBTOTAL formula here. Let’s jump into the method.
Steps:
- Select the entire range and go to the Data tab then select Filter or press CTRL+SHIFT+L.
- After that, click on cell and type =SUBOTAL( and select 2-COUNT.
- Now, type the following formula.
=SUBTOTAL(2,F5:F13)
- Press the ENTER key.
This will return the number of rows which is 9 in this data.
- Now, we will filter the data table with color, follow the process shown in the image below.
- After, clicking OK we will get our result like the following.
If we selected green in the filter option, the process will return the result like this.
This is easy and simple, but whenever you update your data, you need to repeat the process.
Method 2: Excel Formula to Count Cells with Specific Color Using GET.CELL Function
Although Excel has a method, to obtain the fill color of a cell, it is a Macro 4 function from the legacy.
Steps:
- First, go to the Formulas and select Define Name. A dialogue box will pop up.
- Now, type your function Name and type the following formula in the Refers to and click OK.
=GET.CELL(38,'Get Cell'!$B5)
- Now, click on cell G5 and type =GetSpecificColor, and press ENTER key.
After pressing ENTER we will get.
- Now, drag down to AutoFill rest of the series.
It is visible that only the colored cell is defined as a unique number. No filled cells are given zero. We will use COUNTIF to count cell numbers now.
- Now, type the following formula in cell J5.
=COUNTIF($G$5:$G$13,36)
- Now, press the ENTER key.
If we drag down to AutoFill, we will get the result as follows.
Read More: How to Count Colored Cells In Excel Without VBA (3 Methods)
Method 3: Excel Formula to Count Cells with Specific Color Using VBA
Finally, in this article, we will see the VBA method to count specific cells with color.
Steps:
- First, right-click on the sheet and go to View Code.
- After that, copy and paste the VBA code below.
Function MyColorCount(CountRange As Range, CountColor As Range)
Dim CountColorCells As Integer
Dim TotalColorCount As Integer
CountColorCells = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorCells Then
TotalColorCount = TotalColorCount + 1
End If
Next rCell
MyColorCount = TotalColorCount
End Function
- After that, press the F5 or play button to run the code.
- Now, click on J5 and type the following formula.
=MyColorCount($B$5:$B$13,H5)
- Press ENTER key.
- Now, drag down to AutoFill.
Through this code, we are creating a user function MyColorCount which will identify only the colored cell, and using COUNTIF we are calculating the total colored cell number.
Method 4: Find and Select Command to Count Colored Cells
In this section, we will see how to count color cells using the Find and Select command in Excel. Though it’s not any conventional formula, still we’re mentioning it for your ease of experience.
Steps:
- First, go to Find and Select from the Home Tab and click Find.
- After that, a dialogue box will pop up and we will select, Choose Format From Cell….
This will allow us to select colored cells .and select any cell with color in it and click Find All.
As a result, we will get the total number of colored cells and their location in the worksheet, as shown in the following image.
This is a simple method. But we have to repeat the process every time for a different color.
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.
Conclusion
These are 4 different Excel formula to count cells with a specific color. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
