How to Count and Sum Colored Cells in Excel

  • Post author:
  • Post category:Uncategorized

We can count colored cells in excel by using a wide range of functions. In this tutorial, we will learn how to count cells by color and get the sum of colored cells either manually or by doing so across the entire workbook.

Figure 1 – How to count highlighted cells

How to Count Colored Cells Using Filter and SUBTOTAL

This method counts colored cells without VBA 350 0.  To do this:

  • We will insert the formula below into Cell G22


Figure 2 – Count colored cells without vba

Figure 3 – Count colors

  • Now we will select the headers
  • Next, we will go to Data, select Sort and Filter and lastly filter

Figure 4 – How to count colors

  • We will click on any of the filter drop-downs, go to filter by color and select color

Figure 5 – How to count number in colored cells

  • Because we have three colors, the filter will show five cells

Figure 6 – Count colors

  • When we filter the cells, we will notice that the value of the SUBTOTAL will change, and we will view only the cells made visible by our formula

Figure 7 – Using SUBTOTAL to count colors

How to Count Colored Cells Using Countif and GET.CELL Function

The GET.CELL function is a Macro4 function which means we can’t use it directly but must use these steps:

  • We will go to formula and select Define Name
  • In the New Name dialog, we will enter the following:
    • Name: ColorCount
    • Scope: Workbook
    • Refers to: =GET.CELL(38,Sheet1!G4)

Where Sheet 1!G4 is the cell that represents the background color

Figure 8 – Count colors

  • We will click on OK
  • We will name Column H, as ColorCount

Figure 9 – How to use the countif color function

  • In Cell H4, we will enter the formula =CountColor

Figure 10 – Using the count color process

  • We will use the fill handle tool to enter other cells in that column

Figure 11 – How to count colors

  • Our formula will return 0 if there is no background color and a unique number if there is a color in the background
  • From the number indicators, we will fill some cells with the colors we have in our table

Figure 12 – How to count highlighted cells

  • In Cell B22, we will enter the formula below


Figure 13 – Count colors in the worksheet

  • In Cell B23, we will enter this formula below


Figure 14 – Using the fill handle tool to finish the countif color fill function

  • In Cell B24, we will enter this formula below


Figure 15 – Using the fill handle tool to finish the countif color fill function

How to Count Colored Cells using VBA

We can use a VBA macro code to count cells swiftly.

  • We will go to the Developer Tab, navigate to the code group and select Visual Basic

Figure 16 – Using the vba display

  • In the VB Editor, we will right-click on Microsoft Excel Objects and hover around insert
  • Next, we will select Module from the drop-down menu

Figure 17 – Macro box

  • Now, we will copy and paste the VBA code below

Function ColorCount(rColor As Range, rRange As Range)

   Dim rCell As Range

   Dim lCol As Long

   Dim vResult

   lCol = rColor.Interior.ColorIndex

   For Each rCell In rRange

       If rCell.Interior.ColorIndex = lCol Then

           vResult = 1 + vResult

       End If

   Next rCell

   ColorCount = vResult

End Function

Figure 18 – Using VBA to count colored cells

  • We will save and close the VBA to go back to our Worksheet
  • In the adjacent column (Column H), we will fill cells with our desired colors and enter the formula below


Where A10 represents the specific color we want to count and will change with when we have any color change

Figure 19 – How to use Get Color Count function