How to Count Colored Cells in Excel (4 Simple Ways)

In this article, we will show you how to count colored cells in Excel. Using colors in the workbook is a great way to make it look more attractive. But, as there isn’t any built-in function to count the colored cells in Excel, people usually avoid coloring cells. But it can be done with some tricks. Here, we will count the number of the cell with a particular color like the following image.

Download Practice Template

You can download the free practice Excel template from here and practice on your own.

4 Easy Ways to Count Colored Cells in Excel

In this section, you will learn how to count colored cells in Excel by utilizing Excel command tools and User-Defined Functions (UDF). Consider the following dataset, where there are three categories of data, Category: Fruit, Flower and Food. And every category is differentiated by different colors. Fruit category declared in color Blue, category Flower in Orange and category Food has no background colors.

1. Using Find & Select Command 

The Find & Select command is one of the most useful tools in Excel to execute any Excel related tasks. Here, we will utilize it to count colored cells in Excel. Now we will learn how to find out the count of each color that each cell of each category holds.

Steps:

• Firstly, select the dataset with colored cells.
• Secondly, go to the Editing tab from the Home tab.
• Finally, in the Editing tab, select Find & Select -> Find.

• After that, from the pop-up Find and Replace box, click Options.
• Then, click on the drop-down list in Format -> Choose Format From Cell.

• A four-dimensional plus symbol will appear. Place that symbol over any colored cell and click on it (we picked the color Blue).

• Again, the pop-up Find and Replace box will appear, and you will notice, that the Preview* label box will be filled with a color similar to the color of the cell that you picked earlier.
• Finally, click Find All.

• As a result, you will get all the details of the specified colored cells along with the count of that colored cells.

In the same way, you can count all the other colored cells in your worksheet in Excel.

2. Applying Filter Command and the SUBTOTAL Function

Using Excel’s Filter tool and inserting a SUBTOTAL function in it, is another efficient way to count colored cells in Excel. And we can utilize that to count the colored cells in Excel as well. Now we will learn the steps to find out the count of those colored cells in Excel using Filters and the SUBTOTAL function.

Steps:

• To begin with, click on the C12 cell in the worksheet, write the following SUBTOTAL formula,

Here,

102 = The count of the visible cells in the specified range.

B5:B16 = The range of the colored cells.

• You will get the total count of the colored cells in the sheet (e.g. we have 12 cells with background colors, so the SUBTOTAL gave us an output of 12).

• Next, select only the headers of the dataset.
• Thereafter, go to Data -> Filter.

• Consequently, it will insert a drop-down button in each header of the dataset.
• After that, click on the drop-down button from the header of the column that has colored cells in it (e.g. Product Name).
• From the drop-down list, select Filter by Color and you will get all the colors from your dataset in a sub-list.
• Finally, click on the color that you want to count (e.g. we picked the color Blue).

• As a result, it will show you only the cells colored with that specified color along with the counts of those cells in the SUBTOTAL result cell (e.g. there are 4 Blue colored cells in our dataset).

• In the same way, you can count all the other colored cells in your worksheet in Excel (e.g. when we picked the color Orange from the drop-down list, it gave us the cells colored with Orange and as we have 5 cells colored with Orange in our dataset so the SUBTOTAL result cell produced 5)

Read More: Count Cells by Color with Conditional Formatting in Excel (3 Methods)

3. Utilizing GET.CELL 4 Macro and COUNTIFS Functions

The use of Excel 4.0 Macro functions is limited due to its compatibility and difficulty reasons. Another reason is, that this is an old macro function in Excel, so some new features are missing. But if you are still comfortable with working with the EXCEL 4.0 Macros, then we will help you to utilize the function of counting the colored cells in Excel. With the same dataset that we have been practicing on, we will learn how to implement a Macro 4 function to count colored cells in Excel.

Steps:

• First, go to Formulas -> Define Names >> Define Name.

• Next, in the New Name pop-up box, write the following,
  • Name: GetColorCode (this is a user-defined name)
  • Scope: Workbook
  • Refers to: =GET.CELL(38,GetCell!$B5)

Here,

GetCell = Sheet name that has your dataset

$B5 = Reference of the column with the background color.

• Click OK

Now you have a user-defined formula, =GetColorCode.

• Thereafter, in the adjacent to the data, write the formula and press Enter.
• Consequently, it will produce a number (e.g. 42).

• Now, drag the cell down by Fill Handle to apply the same formula to the rest of the cells.

The formula will return specific numbers specified to colors. So all the cells with the same background color will get the same number, and if there is no background color, the formula would return 0. We created a table named Color Count, and in that table, we defined the Cells G5 and G6 according to our color Blue and Orange respectively, and keep the cells next to these (Cells H5 & H6) empty, so that we can get our colored cells count in those cells.

• After that, write the following formula in the H5 cell where you will be having the count of the colored cell,

Here,

$E5:$E$16 = the range of the color code that we extracted from the user-defined formula.

• Then, press Enter.

• As a result, you will get the count of the color-defined cells (e.g. there are 4 Blue colored cells in our dataset, so next to the Blue color-defined cell (G5), it gives us the count 4).
• After that, click on the H6 cell and enter the following formula,

As we have 5 cells colored with Orange in our dataset, the user-defined GetColorCode formula gave us the count 5.

Read More: How to Count Blank Colored Cells in Excel (2 Methods)

4. Implementing VBA Code (a User-Defined Function)

The implementation of the VBA code in Excel-related tasks is the safest and most effective method, thus it requires advanced-level skills from the users. And remember about the new features that we mentioned in the previous Macro 4 section, well, VBA is the advancement of Excel 4.0 macro. Let’s get you started with the implementation of the VBA code to count the colored cells in Excel.

Steps:

• Firstly, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Secondly, in the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window,

  Function Count_Colored_Cells(ColorCells As Range, DataRange As Range)
  Dim Data_Range As Range
  Dim Cell_Color As Long
  Cell_Color = ColorCells.Interior.ColorIndex
  For Each Data_Range In DataRange
  If Data_Range.Interior.ColorIndex = Cell_Color Then
  Count_Colored_Cells = Count_Colored_Cells + 1
  End If
  Next Data_Range
  End Function

This is not a Sub Procedure for the VBA program to run, this is creating a User Defined Function (UDF). So, after writing the code, don’t click the Run button from the menu bar.

• Now, go back to the dataset and define cells with colors as we did in the previous method.
• After that, in the F5 cell, write the following formula,

Here,

Count_Colored_Cells = the user-defined function that you created in the VBA code (Count_Colored_Cells, in the first line of the code).

E5 = Blue color-defined cell

$B5:$B$16 = the range of the dataset with colored cells.

• Press Enter.

You will get the count of the color-defined cells (e.g. there are 4 Blue colored cells in our dataset, so next to the Blue color defined cell (E5), it gives us the count 4).

• Next, drag the cell through the whole column by Fill Handle to get all the counts of your colored cells in the worksheet.

As we have 5 cells colored with Orange in our dataset, the user-defined Count_Colored_Cells function gave us the count 5.

Read More: How to Count Colored Cells in Excel with VBA (4 Easy Methods)

Count Colored Cells in Excel: Knowledge Hub

• Count Colored Cells
• Count Cells with Specific Color
• Count Blank Colored Cells
• Count Cells by Color with Conditional Formatting
• Count Colored Cells in a Row
• Count Colored Cells Without VBA
• Count Colored Cells with VBA

Conclusion

This article showed you how to easily count colored cells in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• How to Count Cells with Specific Text and Fill/Font Color in Excel (4 Ways)
• Excel Formula to Count Colored Cells in a Row (2 Effective Methods)