Excel Formula to Count Cells with Specific Color (4 Ways)

We have attached a sample workbook containing sales information, including Date, Product, Country, Quantity, and Sales.

How to Count Cells with a Specific Color Using Excel Formula: 4 Easy Ways

Method 1 – Using Filters and the SUBTOTAL Function to Count Colored Cells

Steps:

• Select the entire range and go to the Data tab, then select Filter or press Ctrl + Shift + L.

• Click on the result cell and type =SUBOTAL( and select 2-COUNT.

• Use the following formula.

• Press the Enter key.

• This will return the number of rows which is 9 in this data.
• We will filter the data table by color.

• Here’s the result for yellow.

• If we selected green in the filter option, the process will return the result like this.

• Whenever you update your data, you need to repeat the process.

Method 2 – Excel Formula to Count Cells with Specific Color Using the GET.CELL Function

Steps:

• Go to Formulas and select Define Name. A dialog box will pop up.

• Type your function Name and insert the following formula in Refers to, then click OK.

• Click on cell G5 and insert =GetSpecificColor (the formula you named in the previous step), then press Enter.

• Here’s the result, a color code of the reference cell.

• Drag down to AutoFill rest of the series.

• Use the following formula in cell J5.

• Hit Enter.

• If we drag down to AutoFill, we will get the result as follows.

Method 3 – Excel Formula to Count Cells with Specific Color Using VBA

Steps:

• Right-click on the sheet name and go to View Code.

• Copy and paste the VBA code below into the module.

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

• Press F5 or the play button to run the code.
• Click on J5 and use the following formula.

• Press Enter.

• Drag down to AutoFill.

Method 4 – Find and Select to Count Colored Cells

Steps:

• Go to Find and Select from the Home tab and click Find.

• A dialog box will pop up. Select Choose Format From Cell….

• Select any cell with color in it and click Find All.

We will get the total number of colored cells and their location in the worksheet, as shown in the following image.

Practice Section

We’ve attached a practice workbook where you can practice these methods.

Download the Practice Workbook

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