Count Cells By Color in Excel

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.

  1. Method #1 – Count Cells With Color Using Filter Method with Sub Total Function
  2. 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.

  1. 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.read more.

    How to Count Cells with Color in Excel Example 1.1

  2. 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.

    How to Count Cells with Color in Excel Example 1.2

  3. After selecting the subtotal method next, we need to choose a range of cells to be counted.

    How to Count Cells with Color in Excel Example 1.3

  4. OK, now the count of these cells will 7.

    How to Count Cells with Color in Excel Example 1.4

  5. For the above data, the structure applies a filter option.

    How to Count Cells with Color in Excel Example 1.5

  6. 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.

    How to Count Cells with Color in Excel Example 1.6

  7. Choose any of the colors, and we will have those colored rows.

    How to Count Cells with Color in Excel Example 1.7

  8. 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.

    How to Count Cells with Color in Excel Example 1.8

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.read more.

Below is the code I have already written to create a function to count cells based on color.

Code:

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
Example 1.9

All that you have to do is to copy the above code and paste it in your VBA module, as shown below.

How to Count Cells with Color in Excel Example 1.10

Now come back to the worksheet where we have data and create three cells with color.

Example 1.11

Put equal sign and open the function that we have created by using the above code.

How to Count Cells with Color in Excel Example 1.12

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.

Example 1.13

The second argument is in which range of cells we need to count the selected cell color.

How to Count Cells with Color in Excel Example 1.14

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.

Example 1.15

Similarly, do for other colored cells. We get the following result.

How to Count Cells with Color in Excel Example 1.16

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.read more,” 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.

Example 1.17

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>