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**

**=SUBTOTAL(102,G1:G20)**

* 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

**=COUNTIF($H:$H,44)**

* Figure 13 – Count colors in the worksheet*

- In
**Cell B23**, we will enter this formula below

**=COUNTIF($H:$H,6)**

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

- In
**Cell B24**, we will enter this formula below

**=COUNTIF($H:$H,42)**

* 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

**=GetColorCount($A$A4:$A$A20,A10)**

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*

**Instant Connection to an Excel Expert**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment