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.
You want to find the number of completed tasks by counting the green cells.
You can use the steps below to do the job.
- Click the Home tab, open the Find & Select drop-down list on the Editing group, and select the Find option.
Alternatively, press CTRL + F.
The above step opens the Find and Replace feature.
- 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.
The above step hides the Find and Replace feature and shows an eyedropper icon next to the cursor.
- 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.
- 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.
- Click 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.
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.
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.
- 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.
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:
- Select any cell in the dataset, open the Data tab, and click the Filter button on the Sort and Filter group.
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.
- 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.
The above step filters the data in column B to show only green cells.
- Enter the formula =SUBTOTAL(3, B4:B9) in cell B13.
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.
- Select any cell in the table to display the Table Design contextual tab on the Ribbon.
- Open the Table Design tab and select the Filter Button option on the Table Style Options group.
You can then proceed with the steps below.
- Open the Table Design tab and select the Total Row option on the Table Style Options group.
The above step adds a total row at the bottom of the table and the formula =SUBTOTAL(103,[Task Name]) in cell B12.
Note: The SUBTOTALS’s argument of 103 instructs the function to ignore rows hidden by filtering or any other means of hiding rows.
- 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.
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.
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:
- Open the Formulas tab and click the Define Name command button on the Defined Names group.
The above step opens the New Name feature.
- Do the following on the New Name feature:
- Enter an appropriate name for the named range on the Name box, in this case, I have entered ‘GetCellColor.
- Accept the default ‘Workbook’ option on the Scope drop-down list.
- 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.
- Click OK.
- Enter the formula =GetCellColor in the cell range C2:C11.
The formula generates index values for the background color of cells in the range B2:B11.
- Enter the formula =COUNTIF($C$2:$C$11,GetCellColor) in the cell range C13:C15.
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:
- Click the File button on the Ribbon to open the Backstage view.
- Click Options on the left pane of the Backstage view.
The above step opens the Excel Options dialog box.
- 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.
The above step opens the Trust Center dialog box.
- 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 OK on the Excel Options dialog box.
- 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:
- Open the Formulas tab and click the Define Name command button on the Defined Names group.
The above step opens the New Name feature.
- Do the following on the New Name feature:
- Enter an appropriate name for the custom LAMBDA function on the Name box, in this case I have entered ‘GETCOLORINDEX.’
- Accept the default ‘Workbook’ option on the Scope drop-down list.
- Enter the formula =LAMBDA(cell, GET.CELL(63,cell)) on the ‘Refers to’ box.
- Click OK.
You can use the GETCOLORINDEX custom LAMBDA function directly in your dataset as shown below.
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.
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.
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: