How to Count Cells with Color in Excel?
There are multiple ways we can count cells based on the color of the cell in excel.
- Method #1 – Count Cells With Color Using Filter Method with Sub Total Function
- Method #2 – Count Cells with Color By Creating Function using VBA Code
Now let us discuss each of the methods in detail along with an example –
#1 Using Filter Method with Sub Total Function
As I said, we have multiple methods to count cells based on the color of the cell in excel. This method uses the Filter & Subtotal function.
Steps to use the Filter method with subtotal function in excel are as follows.
- First, we need to apply a subtotal function, so at the end of the data, open the SUBTOTAL function in excelSUBTOTAL Function In ExcelThe Excel Subtotal function is a built-in function that returns the subtotal of a given data table or data sets. This formula requires two inputs, the first of which is the function number and the second of which is the range..
- Once we open the SUBTOTAL function, we are supplied with a function list under this; since we are counting cells, choose the u201c2 u2013 COUNTu201d function.
- After selecting the subtotal method next, we need to choose a range of cells to be counted.
- OK, now the count of these cells will 7.
- For the above data, the structure applies a filter option.
- Click on the drop-down list of filters to go to u201cFilter by Color.u201d It will show up all the available colors to filter.
- Choose any of the colors, and we will have those colored rows.
- As youu00a0canu00a0seeu00a0above, our SUBTOTAL function is showing the count of the filtered rows only. Since there are only two rows marked with Orange color, the SUBTOTAL function showing the count as three only; similarly, choose another color and see whether it is working perfectly or not.
This count is 2, so we have an updated result.
#2 Create Function using VBA Coding
As I told you, unfortunately, we don’t have any built-in function, which can help us to count cells based on color in excel. We have told there is no built-in function, but the beauty is we can create our own function by using VBA codingUsing VBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task..
Below is the code I have already written to create a function to count cells based on color.
Function ColorCount(ColorCell As Range, DataRange As Range) Dim Data_Range As Range Dim Cell_Color As Long Cell_Color = ColorCell.Interior.ColorIndex For Each Data_Range In DataRange If Data_Range.Interior.ColorIndex = Cell_Color Then ColorCount = ColorCount + 1 End If Next Data_Range End Function
All that you have to do is to copy the above code and paste it in your VBA module, as shown below.
Now come back to the worksheet where we have data and create three cells with color.
Put equal sign and open the function that we have created by using the above code.
The first argument with this function is “we need to select the color cell that we need to count,” so in this case, I will choose the E2 cell.
The second argument is in which range of cells we need to count the selected cell color.
OK, that’s all we need for this function. Close the bracket and hit the enter key to get the count of the selected cell color.
Similarly, do for other colored cells. We get the following result.
This is done by using VBA “User Defined FunctionUser Defined FunctionUser Defined Function in VBA is a group of customized commands created to give out a certain result. It is a flexibility given to a user to design functions similar to those already provided in Excel.,” it will loop through a provided range of cells and count the number cells which are exactly holding the same color as the provided cell color.
Then it will sum all the matched color cells and returns the result as a count of that color cell.
Like this, we can count cells with colors in excel. There are other methods as well, but those are slightly complicated, so go for the easy one at the start of the learning.
Things to Remember here
- The SUBTOTAL function counts cells only, which are visible, so as the filter applied, it will count only those filtered colored cells.
- VBA code is for the UDF function because there are no built-in functions, so we have to create our own.
This has been a guide to count cells with color in excel. Here we discuss how to count cells with color by using a filter option with subtotal function and VBA code in excel and a downloadable template. You may learn more about excel from the following articles –