|drop-down list filter|
Top 3 Methods to Count Colored Cells In Excel
There is no built-in function to count colored cells in excel, but below mentioned are 3 different methods to do this task.
- Count Colored Cells By Using Auto Filter Option
- Count Colored Cells by using VBA Code
- Count Colored Cells by Using FIND Method
Now, let us discuss each of them in detail –
#1 – Excel Count Colored Cells By Using Auto Filter Option
For this example look at the below data.
As we can see each city is marked with different colors. So we need to count the number of cities based on cell color. Follow the below steps to count cells by color.
- Step 1: Apply the filter to the data.
- Step 2: At the bottom of the data apply the SUBTOTAL function in excel to count cells.
- Step 3: SUBTOTAL function contains many formulas in it, this is helpful if we want to count, sum, average only visible cells data. Now under the heading “PIN” click on the drop-down list filter and select “Choose by Color”.
- Step 4: As we can see all the colors in the data. Now choose the color that you want to filter.
Wow!!! As we can see in the cell D21, our SUBTOTAL function given the count of filtered cells as 6, instead of the previous result of 18.
Similarly, now choose other colors to get the count of the same.
So blue colored cells count is 5 now.
#2 – Excel Count Colored Cells by using VBA Code
VBA’s street smart techniques help us to reduce time consumption at our workplace for some of the complicated issues.
Not only we can reduce time at our workplace we can also create our own functions to fit in our needs. One such function we can create a function to count cells based on color. Below is the VBA code to create a function to count cells based on color.
Function Color_Cell_Count(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 Color_Cell_Count = Color_Cell_Count + 1 End If Next Data_Range End Function
Copy and paste the above code to your module.
This code is not a SUB Procedure to run, rather it is a “User Defined Function” (UDF).
The first line of the code “Color_Cell_Count” is the function name. Now create three cells and color them as below.
Now open the function “Color_Cell_Count” in G2 cell.
Even though we don’t see the syntax of this function, the first argument is what color we need to count, so select the cell F2.
The second argument is to select the range of cells as D2:D19.
Close the bracket and hit the enter key. This will give the count of cells that has the selected cell color.
Like this with the help of UDF in VBA, we can count cells based on cell color.
#3 – Excel Count Colored Cells by Using FIND Method
We can also count cells based on the FIND method as well.
- Step 1: First, select the range of cells where we need to count cells.
- Step 2: Now press Ctrl + F to open the FIND dialogue box.
- Step 3: Now click on “Options>>”.
- Step 4: It will expand the FIND dialogue box. Now click on the “Format” option.
- Step 5: Now it will open up the “Find Format” dialogue box. Click on the “Choose Format From Cell” option.
- Step 6: Now just move your mouse pointer to see the pointer to select the format cell in excel that we are looking to count.
- Step 7: Select the cell which is formatted as desired cell count. I have chosen the F2 cell as the desired cell format and now I can see the preview.
- Step 8: Now click on FIND ALL option to get the count of the selected cell format count of cells.
So, a total of 6 cells were found with selected formatting colors.
Things to Remember
- Provided VBA code is not a Subprocedure in VBA, it is a UDF.
- SUBTOTAL contains many formulas that are used to get the result only for visible cells when the filter is applied.
- We don’t have any built-in function in excel to count cells based on the color of the cell.
This has been a guide to Count Colored Cells in Excel. Here we learn count colored cells by using auto filter option, VBA code, and FIND method and downloadable excel template. You may learn more about excel from the following articles –