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 told we have multiple methods to count cells based on the color of the cell in excel. This method uses the Filter & Subtotal function.
- Step 1: First, we need to apply a subtotal function, so at the end of the data open SUBTOTAL function in excel.
- Step 2: Once we open the SUBTOTAL function we are supplied with a function list under this, since we are counting cells choose the “2 – COUNT” function.
- Step 3: After selecting the subtotal method next we need to choose a range of cells to be counted.
- Step 4: OK, now the count of these cells will 7.
- Step 5: For the above data, the structure applies a filter option.
- Step 6: Click on the drop-down list of filters to go to “Filter by Color”. It will show up all the available colors to filter.
- Step 7: Choose any of the colors and we will have those colored rows.
- Step 8: As you can see above, our SUBTOTAL function is showing the count of the filtered rows only. Since there are only two rows marked with Orange color, SUBTOTAL function showing the count as 3 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 beauty is we can create our own function by using VBA coding.
Below is the code I have already written to create a function to count cells based on color.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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 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 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 Function”, 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 filter option with subtotal function and VBA code in excel and a downloadable template. You may learn more about excel from the following articles –