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

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. In this article, we will show you how to count colored cells in Excel.


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

1. Use the Find & Select Command to Count Colored Cells in Excel

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.

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.

Now we will learn how to find out the count of each color that each cell of each category holds.

Steps:

  • Select the dataset with colored cells.
  • In the Editing tab, select Find & Select -> Find

count colored cells in excel by find and select

  • From the pop-up Find and Replace box, click Options.

  • From the next pop-up Find and Replace box, 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).

count blue colored cells in excel by find and select

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

You will get all the details of that specified colored cells along with the count of that colored cells.

result of count colored cells in excel by find and select

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


2. Apply Filters and the SUBTOTAL Function in Excel to Count Colored Cells

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.

Consider the following dataset which is colored by the Category. Now we will learn the steps to find out the count of those colored cells in Excel using Filters and the SUBTOTAL function.

Steps:

  • In another cell in the worksheet, write the following SUBTOTAL formula,
=SUBTOTAL(102,B5:B16)

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

count colored cells in Excel with subtotals

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

count colored cells in Excel with filters

  • It will insert a drop-down button in each header of the dataset.

inserting drop down to count colored cells in Excel

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

  • Click on the color that you want to count (e.g. we picked the color Blue).
  • 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).

result count blue colored cells in Excel

  • 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)

result count orange colored cells in Excel


3. Implement GET.CELL 4 Macro and COUNTIFS Functions in Excel to Count Colored Cells

The use of Excel 4.0 Macro functions is limited due to its compatibility and difficulty reasons. Another reason is, 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 in 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.

  • Go to Formulas -> Define Name.

  • 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

count colored cells in Excel with get.cell

Now you have a user defined formula, =GetColorCode.

  • In the adjacent to the data, write the formula and press Enter.
  • It will produce a number (e.g. 42).

getting code to count colored cells in Excel

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

  • Now define those colors in other cells in the same worksheet to get the count.

See the picture below to understand more.

define colored cell to count colored cells in Excel

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.

  • Write the following formula in the cell where you will be having the count of the colored cell,
=COUNTIFS($E5:$E$16,GetColorCode)

Here,

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

  • Press Enter.

result to count colored cells in Excel

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

  • Now 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 GetColorCode formula gave us the count 5.


4. Embed VBA Code (a User-Defined Function) to Count Colored Cells in Excel

The implementation of the VBA code in Excel-related tasks is the most safest and 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:

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

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

VBA code to count colored cells in Excel

  • Now go back to the dataset and define cells with colors like the previous section (step no 6 in the implementation of the CELL Macro 4 function).

  • In the cell, write the following formula,
=Count_Colored_Cells(E5,$B$5:$B$16)

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

$E5:$E$16 = the range of the dataset with colored cells.

  • Press Enter.

result of count blue colored cells in Excel

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

  • Now 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.


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.


You May Also Like To Explore

Sanjida Ahmed

Hello, this is Sanjida, an Engineer who loves Sports a lot. Here I try to solve Excel problems with you. Hope I could be a great help to you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo