In this article we’ll learn how to count colored cells in excel, how to use excel countif cell color and count highlighted cells in excel.

Sometimes you highlight certain rows for your reference and you then want to count colored cells in excel which you highlighted.

Now this is possible but the implementation is a bit different than what you think. There is no specific function that you can use for counting colors in excel. But it can be done using a combination of other things.

Let’s see how to count color cells in excel.

We have three main ways to count coloured cells in Excel:

1. SUBTOTAL Function & Filtering
2. CELL Function to count cells with color
3. Creating custom function in VBA

Count Colored Cells in Excel using SUBTOTAL & Filter

We have the following dataset where we have highlighted some rows by coloring them- blue & green.

1. Chose a cell and type =SUBTOTAL(103,A2:A20)
   
   
   
2. Select the first row, go to Data tab under Sort and Filter and click Filter. A filter will be applied to all headers or the top row.
   
   
   
3. Click any filter drop down. And go to ‘Filter by Color’ and select the color. As we have used 2 colors it’s showing us 2 options the more colored rows you add the more options you’ll see here.
   
   
   
4. Once you have filtered the cells you can see the cell where we used SUBTOTAL function to count cells with color has changed. This returns the number of cells visible on screen i.e. filtered green color cells.
   

Now you might be wondering what the 103 in the function is about. 103 is used as first argument and it tells Excel to only count visible cells and ignore hidden rows on screen. If you unfilter the data you’ll that it’ll return the value of 15.
Read more about SUBTOTAL function to understand how it works.

GET.CELL to count color cells in Excel

Now the GET.CELL function can’t be used as a regular function. It can be used in Excel named ranges.

You can see this forum thread to understand more about this function.

Now using GET.CELL to count colored cells in excel is a three step process, each steps also has mini steps. Let’s deep dive:

1. Creating Named Range
   1. Select the range of cells, go to Formulas tab and select Define Name.
      
      
      
   2. In the dialog box, enter the following details:
      Name: GetBgCells
      Scope: Workbook
      Refers to: =GET.CELL(63,Sheet24!$A2)
      I have used Sheet24!$A2 for reference, you have to choose the reference of column having background color.
      
      
      
2. Using GetBgCells to get color code in excel
   1. In an empty column, go to second row and type =GetBgCells and drag the formula down.
      
      Now what you can see is that the formula has returned 0 where the background color is empty or white. While for blue & green colored cells have a different number or color code in excel.
      
      
3. Counting colored cells using Excel COUNTIF cell color
   
   37. In column A (or whichever column you referred while creating a named range) in 2 cells add the colors which you had used to highlight cells. In my case it’s blue & green the same ones which I used. Green rows has the color code 36 and blue ones have 37.
       Now in the adjacent cells type =COUNTIF($F$2:$F$20,36)
       This will count highlighted cells in excel i.e. count the green colored cells. If I wanted to count blue cells I would have put 37 instead of 36 in the COUNTIF formula.
       

The COUNTIF function used GetBgCells named range. The named range refers to adjacent cell in column A containing the background color. $E$2:$E$15 refers to the column containing the excel color code of all cells.

Count color cells in Excel using VBA custom function

Till now we just saw how to count colored cells in excel without using any VBA code. But if already know VBA than it’s the easiest method.

Here’s the code:

Function CountColoredCells(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
CountColoredCells = TotalCount
End Function

Here are the steps:

1. Press ATL + F11 or click worksheet tab and select View Code to open VB editor.
   
   
2. Under left pane, right click the sheet you’re working on click Insert and select Module. A new window will open.
   
   
   
3. Now copy and paste the code here and close the VB editor.
   
   
   
4. Now we have created a custom function by the name of CountColoredCells.
   
   
5. In your sheet, take an empty color and insert the color which you want to count and now type =CountColoredCells($A$2:$A$20, B22)
   
   B22 has the green color which our custom function will pick up to match all rows with.
   
   
6. This will count colored cells in excel which are green.

Our custom function has the following syntax:

=CountColoredCells(Range, CountColor)

Range: is the range of cells containing color which you want to count.
CountColor: is the color for which you want to count cells.

Counting Highlighted Cells in Excel- Conditional Formatting

Most of the above methods that you just saw to count cells with color doesn’t work if you have highlighted cells using conditional formatting.

The above ways only work when you have changed colors of cells (rows or columns) via changing font styles or background colors of cells.

Now the way to count highlighted cells in excel from conditional formatting is nearly same as method 1:

1. First find the cells you want to highlight and use conditional formatting to highlight them.
   Here we have highlighted cells which have the word ‘North’ or you can say we wanted to highlight employees who handle the North region.
   
   
   
2. Chose a cell and type =SUBTOTAL(103,A2:A20)
   
   
   
3. Select the first row, go to Data tab select Sort and Filter and click Filter. A filter will be applied to all headers or the top row.
   
   
   
4. Click any filter drop down. And go to ‘Filter by Color’ and select the color. As we have used yellow color it’s showing us the option for yellow, the more colored rows you add the more options you’ll see here.
   
   
   
5. Once you have filtered the cells you can see the cell where we used SUBTOTAL function to count cells with color has changed. This returns the number of cells visible on screen i.e. filtered yellow color cells.
   

This is how counting highlighted cells in excel is done.

Conclusion

That was all about how to count colored cells in excel.

I hope this article was helpful to you and explained to you the ways to count color cells in excel, excel count highlighted cells and much more.

If you have any questions or tips, I’d love to hear them in the comment section below.