Count Colored Cells in Excel

You may get a dataset with colored cells done manually or using conditional formatting to highlight specific data, such as completed tasks, pending approvals, or key milestones.

To analyze this data, you might need to count the number of colored cells—for example, to track the number of completed tasks. 

Although Excel doesn’t have a built-in function to count colored cells, I’ll show you effective methods to do the job. 

Method #1: Use the Find and Replace to Count Colored Cells

You can use the Find and Replace feature to determine the number of cells in a dataset with a particular fill color.

Suppose you have the dataset below where tasks are color-coded based on their status: green for completed tasks, yellow for tasks in progress, and red for pending tasks.

Data set with colored cells in Excel

You want to find the number of completed tasks by counting the green cells.

You can use the steps below to do the job.

  1. Click the Home tab, open the Find & Select drop-down list on the Editing group, and select the Find option.
Click on the find option in the find and replace drop down menu

Alternatively, press CTRL + F.

The above step opens the Find and Replace feature.

  1. On the Find and Replace feature, click the black down arrow on the Format command button at the far right of the ‘Find what’ drop-down and select the ‘Choose Format From Cell’ option.
Click on the format button then click on choose format from cell in Excel

The above step hides the Find and Replace feature and shows an eyedropper icon next to the cursor.

Cursor changes into an eye dropper icon
  1. Move the transformed cursor over any green cell in the dataset and click it.

Notice that Excel redisplays the Find and Replace feature and returns the cursor to its normal appearance. The ‘Preview’ button changes color to reflect your format selection.

Preview button changes color to reflect the selected format
  1. Select the range in which you want to count the colored cells, in this case, range B2:B11.

Note: If you want to count colored cells in the entire worksheet, leave out the above step and go to the step below.

  1. Click the Find All button.
Click on the find all button

The Find and Replace feature displays the total number of cells matching the color you selected in the bottom-left corner of the window.

The total number of colored cells are shown in the bottom of find and replace dialog box

Note: Remember to return the Find and Replace feature to its original state by clicking the black down arrow on the Format command button and selecting the ‘Clear Find Format’ option.

Click on clear find format

If you don’t clear the find format, the Find and Replace feature may produce misleading results in subsequent searches in the current Excel session.

  1. To close the Find and Replace feature, click the ‘X’ icon at the top-right corner of the window or press the Esc key.

Note: This method is not dynamic; you will need to repeat the process each time you want to find the count of specific colored cells.

Also read: How to Find Merged Cells in Excel?

Method #2: SUBTOTAL Function to Count Colored Cells

You can filter a target dataset by color and use the SUBTOTAL function to calculate the number of colored cells.

Suppose you have the dataset below where tasks are color-coded based on their status: green for completed tasks, yellow for tasks in progress, and red for pending tasks.

Data set with colored cells to count in Excel

You want to calculate the number of completed tasks by counting the green cells and display the result in cell B13.

Here’s how to do it:

  1. Select any cell in the dataset, open the Data tab, and click the Filter button on the Sort and Filter group.
Click on the filter icon in the ribbon

If you prefer using keyboard shortcuts press CTRL + SHIFT + L.

The above step turns on filtering for the dataset, displaying the filter and sort buttons next to column headers in the first row.

  1. Click the filter and sort button next to the column header in column B, hover over the Filter by Color option, and click the green color on the Fill by Cell Color submenu.
Hover over the filter my color option and then filter by the selected color

The above step filters the data in column B to show only green cells.

  1. Enter the formula =SUBTOTAL(3, B4:B9) in cell B13.
Enter the sub total formula to get the count of only the filtered colored cells

The SUBTOTAL function returns the value 3 in cell B13, the total count of green cells in the dataset corresponding to the number of completed tasks.

Note: The first argument of the SUBTOTAL function (3) instructs the function to ignore rows hidden only by filtering. It will not ignore rows hidden by any other means.

How to Count Colored Cells in a Table

If your dataset is in table format, you can skip step #1 above, as filtering is already enabled by default.

However, if you don’t see the filter and sort buttons in the first row of the table, you can enable them using the steps below.

  1. Select any cell in the table to display the Table Design contextual tab on the Ribbon.
  2. Open the Table Design tab and select the Filter Button option on the Table Style Options group.
check that the filter option is checked

You can then proceed with the steps below.

  1. Open the Table Design tab and select the Total Row option on the Table Style Options group.
Select the total row option

The above step adds a total row at the bottom of the table and the formula =SUBTOTAL(103,[Task Name]) in cell B12.

A total row is added with the subtotal formula that only counts the colored cells

Note: The SUBTOTALS’s argument of 103 instructs the function to ignore rows hidden by filtering or any other means of hiding rows. 

  1. Filter the data in column B by color as explained in step #2 of the previous section above.

The SUBTOTAL function returns the value 3 in cell B12, the total count of green cells in the dataset.

Filter the data set to get the colored cells count yeah and I get my eyes at home
Also read: How to Filter by Color in Excel?

Method #3: Legacy GET.CELL Function to Count Colored Cells

You can use the legacy GET.CELL function to count colored cells in Excel.

The GET.CELL function is part of the Excel 4.0 (XLM) macros, which were introduced in earlier versions of Excel. This function retrieves specific information about a cell, such as its format, content type, color, or formula. 

The syntax of the GET.CELL function is ‘GET.CELL(type_num, reference).’ The ‘type_num’ argument is a number that specifies the type of information you want to retrieve about a cell.

The ‘reference’ argument is the cell or range of cells for which you want to retrieve information. 

While you can no longer use the GET.CELL function directly in Excel formulas, you can use it indirectly through named ranges.

Suppose you have the dataset below on Sheet1 where tasks are color-coded based on their status: green for completed tasks, yellow for tasks in progress, and red for pending tasks.

Data set to calm colored service

You want to calculate the count of completed tasks (green), in progress (yellow), and pending tasks (red), and display these values in cells C13, C14, and C15, respectively.

Here’s how to do it:

  1. Open the Formulas tab and click the Define Name command button on the Defined Names group.
Click on the define name option in the ribbon

The above step opens the New Name feature.

  1. Do the following on the New Name feature:
  1. Enter an appropriate name for the named range on the Name box, in this case, I have entered ‘GetCellColor.
  2. Accept the default ‘Workbook’ option on the Scope drop-down list.
  3. Enter the formula `=GET.CELL(63,Sheet1!$B2)` in the ‘Refers to’ box. The argument ’63’ tells Excel to retrieve the index of the background color of the target cell. ‘Sheet1!$B2’ is the reference to the column where the background color index is to be obtained. The column reference (B) is absolute, meaning it will remain fixed when copied to other columns, while the row reference (2) is relative, meaning it will adjust when copied to other rows.
  4. Click OK.
Enter the GET.CELL formula in the Refers to field
  1. Enter the formula =GetCellColor in the cell range C2:C11.
Enter the GET.CELL formula in a cell

The formula generates index values for the background color of cells in the range B2:B11.

  1. Enter the formula =COUNTIF($C$2:$C$11,GetCellColor) in the cell range C13:C15.
Use the countif formula to count the color index values

The formula returns 3 for completed tasks (green), 3 for tasks in progress (yellow), and four for pending tasks (red).

Notice that cells B13, B14, and B15 have the same background color as the cells I want to count, and they are located in the same column I used to create the ‘GetCellColor’ named range.

Note: Save the workbook as a Macro-Enabled Workbook(*.xlsm) to retain the ‘GetCellColor’ named range in the workbook for future use.

How the Formula Works

The formula  =COUNTIF($C$2:$C$11, GetCellColor) counts the number of cells in the range $C$2:$C$11 that have the same background color as the one specified by the ‘GetCellColor’ named range. 

The background color is identified by its index, which is returned by the ‘GetCellColor’ named range. 

The range reference ‘$C$2:$C$11’ is absolute, meaning it remains fixed and does not change when the formula is copied to other cells.

Note: The formula does not update automatically when there are changes in the cell range targeted by ‘GetCellColor’ named range. If you trigger a recalculation by pressing CTRL + ALT + F9 Excel throws a #BLOCKED! error as shown below.

The #BLOCKED! error occurs because the GET.CELL function relies on Excel 4.0 macros which are blocked by default in modern versions of Excel.

How to Enable Excel 4.0 Macros in Excel

You will need to enable Excel 4.0 macros in Excel so that the =GetCellColor formula recalculates when you trigger recalculation. 

Here’s how to enable the Excel 4.0 macros:

  1. Click the File button on the Ribbon to open the Backstage view.
  2. Click Options on the left pane of the Backstage view.
Click on the options in the file tab in the backstage view in Excel

The above step opens the Excel Options dialog box.

  1. On the Excel Options dialog box, click the Trust Center category on the left pane and the Trust Center Settings command button on the right.
click on trust center and then click on trust center settings

 The above step opens the Trust Center dialog box.

  1. On the Trust Center dialog box, click the Macro Settings category on the left pane, select the ‘Enable Excel 4.0 macros when VBA macros are enabled’ checkbox on the right, and click OK.
Click on macro settings and then click on enable Excel 4.0 macros when VBA Macros are enabled
  1. Click OK on the Excel Options dialog box.
  2. Close and reopen the Excel file for changes to take effect.

When there are changes in your data you can press CTRL + ALT + F9 and the formula will recalculate as expected.

Another Way of Using the GET.CELL Legacy Function to Count Colored Cells

You can create a custom LAMBDA function to create a custom function that returns the index of a cell’s background color.

The syntax of the function  is  =LAMBDA([parameter1, parameter2, …,] calculation).

The custom LAMBDA function you create will use the result obtained by the GET.CELL function as its calculation argument. 

You can use the custom LAMBDA function directly in a worksheet to get the index of a cell’s background color.

The LAMBDA function is only available in recent versions of Excel.

Here’s how to create the LAMBDA function:

  1. Open the Formulas tab and click the Define Name command button on the Defined Names group.
Click on the define name option in the formulas tab

The above step opens the New Name feature.

  1. Do the following on the New Name feature:
  1. Enter an appropriate name for the custom LAMBDA function on the Name box, in this case I have entered ‘GETCOLORINDEX.’
  2. Accept the default ‘Workbook’ option on the Scope drop-down list.
  3. Enter the formula =LAMBDA(cell, GET.CELL(63,cell)) on the ‘Refers to’ box. 
  4. Click OK.
Enter the lambda function in the name dialog box

You can use the GETCOLORINDEX custom LAMBDA function directly in your dataset as shown below.

Use the newly created lambda function in the cell to get the color index of the cell

Notice that using a LAMBDA function makes your formula more readable.

You can now use the color indexes as the criteria argument for the COUNTIFS function to count the colored cells. 

For example, you can use the value 50, which represents the index of the green background color, in the COUNTIFS function to count the green cells in the dataset. This corresponds to the number of completed tasks, as shown below.

Use the counter function to get the total number of cells of the specified color

Note: Save the workbook as a Macro-Enabled Workbook(*.xlsm) to retain the LAMBDA function in the workbook.

Also read: Count Cells that are Not Blank in Excel

Method #4: VBA User-Defined Function to Count Colored Cells

You can use the User-Defined Function below to count colored cells in Excel.

Function COUNTCOLORED(CountRange As Range, FillCell As Range)
Dim Count As Long
Dim FillColor As Long
Dim i As Range
FillColor = FillCell.Interior.ColorIndex
For Each i In CountRange
    If i.Interior.ColorIndex = FillColor Then
        Count = Count + 1
    End If
Next i
COUNTCOLORED = Count
End Function

You can use the function just like any other built-in function in Excel. 

The function’s syntax is =COUNTCOLORED(Countrange, FillCell) where Countrange is the cell range where you want to count colored cells and FillCell is the cell with the background color of the cells you want to count. The FillCell argument is the same cell where you enter the function formula.

In the example below I used the COUNTCOLORED function to calculate the count of green cells in the dataset.

VPA function to count colored cells in Excel

Notice that the cell C13 in which I entered the formula has the same background color as the colored cells I wanted to count.

I have shown you different ways to count colored cells in Excel. I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment