Watch Video – How to Count Colored Cells in Excel
Wouldn’t it be great if there was a function that could count colored cells in Excel?
Sadly, there isn’t any inbuilt function to do this.
It can easily be done.
This Tutorial Covers:
How to Count Colored Cells in Excel
In this tutorial, I will show you three ways to count colored cells in Excel (with and without VBA):
- Using Filter and SUBTOTAL function
- Using GET.CELL function
- Using a Custom Function created using VBA
#1 Count Colored Cells Using Filter and SUBTOTAL
To count colored cells in Excel, you need to use the following two steps:
- Filter colored cells
- Use the SUBTOTAL function to count colored cells that are visible (after filtering).
Suppose you have a dataset as shown below:
There are two background colors used in this data set (green and orange).
Here are the steps count colored cells in Excel:
- In any cell below the data set, use the following formula: =SUBTOTAL(102,E1:E20)
- Select the headers.
- Go to Data –> Sort and Filter –> Filter. This will apply a filter to all the headers.
- Click on any of the filter drop-downs.
- Go to ‘Filter by Color’ and select the color. In the above dataset, since there are two colors used for highlighting the cells, the filter shows two colors to filter these cells.
As soon as you filter the cells, you will notice that the value in the SUBTOTAL function changes and returns only the number of cells that are visible after filtering.
How does this work?
The SUBTOTAL function uses 102 as the first argument, which is used to count visible cells (hidden rows are not counted) in the specified range.
If the data if not filtered it returns 19, but if it is filtered, then it only returns the count of the visible cells.
#2 Count Colored Cells Using GET.CELL Function
GET.CELL is a Macro4 function that has been kept due to compatibility reasons.
It does not work if used as regular functions in the worksheet.
However, it works in Excel named ranges.
See Also: Know more about GET.CELL function.
Here are the three steps to use GET.CELL to count colored cells in Excel:
- Create a Named Range using GET.CELL function
- Use the Named Range to get color code in a column
- Using the Color Number to Count the number of Colored Cells (by color)
Let’s deep dive and see what to do in each of the three mentioned steps.
Creating a Named Range
- Go to Formulas –> Define Name.
- In the New Name dialog box, enter:
- Name: GetColor
- Scope: Workbook
- Refers to: =GET.CELL(38,Sheet1!$A2)
In the above formula, I have used Sheet1!$A2 as the second argument. You need to use the reference of the column where you have the cells with the background color.
Getting the Color Code for Each Cell
In the cell adjacent to the data, use the formula =GetColor
This formula would return 0 if there is NO background color in a cell and would return a specific number if there is a background color.
This number is specific to a color, so all the cells with the same background color get the same number.
Count Colored Cells using the Color Code
If you follow the above process, you would have a column with numbers corresponding to the background color in it.
To get the count of a specific color:
- Somewhere below the dataset, give the same background color to a cell that you want to count. Make sure you are doing this in the same column that you used in creating the named range. For example, I used Column A, and hence I will use the cells in column ‘A’ only.
- In the adjacent cell, use the following formula:
This formula will give you the count of all the cells with the specified background color.
How Does It Work?
The COUNTIF function uses the named range (GetColor) as the criteria. The named range in the formula refers to the adjacent cell on the left (in column A) and returns the color code for that cell. Hence, this color code number is the criteria.
The COUNTIF function uses the range ($F$2:$F$18) which holds the color code numbers of all the cells and returns the count based on the criteria number.
#3 Count Colored Using VBA (by Creating a Custom Function)
In the above two methods, you learned how to count colored cells without using VBA.
But, if you are fine with using VBA, this is the easiest of the three methods.
Using VBA, we would create a custom function, that would work like a COUNTIF function and return the count of cells with the specific background color.
Here is the code:
'Code created by Sumit Bansal from https://trumpexcel.com Function GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function
To create this custom function:
- With your workbook active, press Alt + F11 (or right click on the worksheet tab and select View Code). This would open the VB Editor.
- In the left pane, under the workbook in which you are working, right-click on any of the worksheets and select Insert –> Module. This would insert a new module. Copy and paste the code in the module code window.
- Double click on the module name (by default the name of the module in Module1) and paste the code in the code window.
- Close the VB Editor.
- That’s it! You now have a custom function in the worksheet called GetColorCount.
To use this function, simply use it as any regular excel function.
Syntax: =GetColorCount(CountRange, CountColor)
- CountRange: the range in which you want to count the cells with the specified background color.
- CountColor: the color for which you want to count the cells.
To use this formula, use the same background color (that you want to count) in a cell and use the formula. CountColor argument would be the same cell where you are entering the formula (as shown below):
Note: Since there is a code in the workbook, save it with a .xls or .xlsm extension.
Do you know any other way to count colored cells in Excel?
If yes, do share it with me by leaving a comment.
You May Also Like the Following Excel Tutorials: