Sometimes you may use different colored cells in Google Sheets to represent categories. This makes your data easy to read. In such cases, it can be useful to count the cells based on the cell’s color. For instance, we have a list of student names and they’re colored. If the cell is red in color, the student is absent. If the cell is green in color, the student is present.

If you want to know the number of students present, it’d be useful if you could simply count the green-colored cells, right? So we will show you how to do that in easy steps, plus there are four different ways in which you can count the colored cells.

Let us begin!

**4 Methods of Counting Colored Cells in MS Excel:**

- Using SUBTOTAL and filters
- Using VBA code
- Using the FIND function
- Using COUNTIF and GET.CELL

**Method #1: Using SUBTOTAL and Filters**

*This method uses the SUBTOTAL formula to calculate the total number of cells. You can then use the filter to only see cells of a particular color.*

**Step #1: Pick a cell to display the count**

Select a cell where you want to see the results of counting all the colored cells.

In our example, we’ve chosen **C1** to display the count. You can see the selected cell in the **Name Box** on the top left corner just below the toolbar.

**Step #2: Apply the SUBTOTAL formula**

Capture the subtotal formula in cell C1.

The formula is [**=SUBTOTAL(function_name, ref1)]**.

**function_name** is represented by numeric values 1 to 11 and 101 to 111.

For this purpose, you can use either 102 or 103. You can use 102 if you’re counting cells with numeric data. You can use 103 if you’re counting cells with text data.

Click on cell C1.

Go to the **Formulas** tab in the top menu bar and select **Math & Trig**, scroll down to **SUBTOTAL** and click on it.

Now, fill the **Function_num** and **Ref1** fields and click on **OK**.

For our example, we will use function_num 103 and the Fields are A2 to A20 (A2:A20).

Here, the formula would be **=SUBTOTAL(103, A2:A20)**.

Alternatively, you can directly type the formula into the cell.

**Step #3: Press Enter**

If you have used the inserted the formula using the menu bar, click on the **OK** button.

If you have typed in the formula, press **Enter** to see the formula in action.

You would now see the total count of all the cells.

**Note: **The SUBTOTAL formula with function numbers between 101 and 111 calculates *visible* cells. If you use another formula, it will count the number of cells regardless if they are visible or not.

**Step #4: Apply filter by cell color**

Now let’s apply a filter for the green-colored cells to get the count of students who are present.

Under the main menu **Home **tab, click **Sort & Filter**, and then from the dropdown, select **Filter**.

Alternatively, you can click on the alphabetic header row and press **Ctrl+Shift+L**.

Once you have applied a filter to your table, you will see a small block with a downward pointing arrow next to each header cell. This is the Filter Icon.

Click on the Filter Icon.

Select **Filter by Color**.

Click on the color you want to filter and have visible.

In our example, we picked the green color.

Click on the **OK** button.

You will now only see the green-colored cells in Excel.

In addition, the SUBTOTAL formula in C1 now only gives you the total number of green cells.

**Method #2: Using VBA code**

*In this method, you create a custom or user-defined function. With the created function, you can count the colored cells.*

**Step #1: Select Visual Basic**

Go to the **Developer** tab in the main menu bar and select **Visual Basic**.

**Step #2: Create a module**

You can now see a visual basic application dialog box.

Click on the minus sign at the top left.

Click on **Insert** and from the submenu that appears, select **Module**.

**Step #3: Copy-paste the VBA code**

Once you click on **Module**, a dialog box will be launched. Copy the following code.

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

Now paste the code into the dialog box.

Close the dialog box by clicking on the red **X** icon at the top-right corner.

**Step #4: Apply the code in your spreadsheet**

Color two cells, the same as the source formatting. In our example, that would be red and green.

C2 is red in color and D2 will represent the number of red-colored cells.

C3 is green in color and D3 will represent the number of green-colored cells.

Place your cursor in D2, and enter the formula **=Color_Cell_Count(C2, A2:A20)**.

**C2** represents a cell with the color you want to count, while **A2:A20 **represents the range of cells to be counted.

**Step #5: Press Enter**

Press **Enter** to see the count.

Similarly, copy the same formula onto the next cell to find the number of green-colored cells.

**Method #3: Using the FIND function**

*In this method, you use the **FIND **function to get the number of cells based on formatting. Here, the formatting is colored cells.*

**Step #1: Select the range of cells**

Select the range of cells by clicking and dragging your cursor across them.

The **Name Box** will show you the selected range.

**Step #2: Open ‘Find and Replace’ dialog box**

Press** Ctrl **and the letter **F** on your keyboard simultaneously to open the **Find and Replace** dialog box.

Select **Options** in the dialog box.

**Step #3: Go to ‘Format’**

Click on the **Format** button.

This will open the **Find Format** dialog box.

**Step #4: Select ‘Choose Format From Cell’**

In the **Find Format** dialog box, at the bottom you will see the ‘**Choose Format From Cell…’ **option**.**

Click on it.

The window will now minimize, and your mouse cursor will change to look like a pen icon.

Tap on any cell with the color that you want to count.

In our example, we pick a green-colored cell.

**Step #5: Select ‘Find All’**

The **Find and Replace** window will now open again, where you will see the chosen color in **Preview**.

Click on the **Find All** button to find all such cells with the same format.

You will see a list of all the green-colored cells along with their count at the bottom of the window.

**Method #4: Using COUNTIF and GET.CELL**

*In this method, we first get the unique color code and then count the cells using the color code.*

**Step #1: Go to ‘Formulas’ and click on ‘Define Name’**

In the **Formulas** tab in the main menu bar, click on **Define Name**.

Select **Define Name** again from the dropdown that appears.

**Step #2: Enter details for Define Name**

A new window will appear, with fields to be completed.

In the Name: field, enter any name you like. Here, we’ve entered **ColorCode**.

Type the formula **=GET.CELL(38,Sheet1!$A2)** in the ‘Refers to:’ field.

In our example, 38 represents the unique color number in Excel.

The next parameter is the cell reference, i.e. cell **A2** in **Sheet 1**.

Click on the **OK** button once you’re done.

**Step #3: Get the color code**

Now go to your Excel sheet.

In the next column, B, in our example, enter **=ColorCode**.

In your case you would use the name you assigned.

Now, drag the fill handle across all the rows with data. The fill handle is a the little square at the bottom right of your selected cell.

This would ensure you get the code for all your colored cells.

**Step #4: Apply the COUNTIF formula**

In column D, fill two cells with the same colors as in Column A.

Next you will count the different colors matching the colors in column D.

Place your cursor in column E, next to the colored cell in column D, and enter the formula.

The formula is **=COUNTIF(range, criteria)**.

We use the color code to calculate.

In our example, the **range** is from **B2** to **B20**.

**Criteria** is the name we created, **ColorCode**.

Therefore our formula would become **=COUNTIF(B2:B20, ColorCode)**.

**Step #5: Press Enter**

Once you’re done entering the formula, press **Enter**.

This will give you the count of cells with the color in the cell left to it.

Drag the fill handle down to the next cell, to get the count for the other color as well.

**The Bottom Line**

No more struggling to count the number of colored cells. These four methods will help you get the count of the colored cells in no time!