*In this article, you will understand how in Excel you can count or sum a number of cells or row based on colors background without using VBA even If a data set has two or more colors. *

The article is helpful only when a user color cells

without using conditional format rule.

*We will investigate three different ways to count color cells in Excel.*

- While writing this I used
**Office 365**, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content. **Note:**None of them is a perfect solution to count color cells in Excel, and each has its caveat.

Let’s start with the easiest and widely used option by using Excel’s **Find and Replace** feature.

**Count or Add Colors using the Find option**

In worksheet **Find**, you have a data set of Students and their Attendance where Absent is manually mark with Red background.

The **assignment** is to compute a number of cells with a red shade (Absent).

**To Count Excel cells using the Find option**

- Go to worksheet ‘
**Find**’ of Excel working file*(Image instructions below)* - Select range of cells
**C4:C23** - Go to Home Tab and select
**Find & Select**dropdown and Click**Find…**that open**Find and Replace**dialogue box (*Shortcut-***Ctrl+F**) - Select the
**Format**button - Click the
**Choose Format From Cell..**of**Find Format**dialogue box - Select the
**Red format cell**using*new plus sign with an eyedropper pointer* - Click
**Find All**button

We can see that the number of red cells is four that means we have four absent students in the class. Although this is an Example with a small data set, we can easily replicate the same steps with large data sets.

- Try to replicate the query with a separate Workbook. The Excel solution file is just for reference.
- These steps are simple but require
**manual interference**every time to count color cells. - In case of multiple colors this method will be
**unmanageable**at some point in time.

**Count color cells using Table filter**

The second method is dynamic and does not require same level of manual Input. We will be using the Excel filter along with SUBTOTAL function on an Excel Table. This method is suitable for counting more than two colors of a given data set.

In worksheet **Table**, you have a data set of Students and their Attendance. Attendance is marked with three categories ‘Present’, ‘Leave’ and ‘Absent’ with Green, Yellow and Red colors respectively.

The **assignment** is to return count cells with any of the selected color backgrounds.

**To count color cell using Table Filter**

- Go to worksheet ‘
**Find**’ of Excel working file*(Image instructions below)* - Select
**any cell of data set**and convert it to Excel Table using**Ctrl+T**shortcut - Mark
**Total Row** - Select Attendance filter Icon
- Hover to
**Filter by Color**and Select**Yellow**color - Subtotal function return count of 3
*(only visible cell)*

This is a useful function to count cells automatically by converting List into Table. Any addition or subtraction of row will return new cell count.**The caveat** is that you must select drop down multiple times to see the number of counts for each color.

So, as above you learn how to use count cells with colors and now read with sum.

Same steps will be followed for sums numbers like in Attendance column instead of text (Absent, Present) there will be attendance in number i.e. total days come in one month then ** use filter** to show results of student with least attendance. For this we will choose

**formula.**

*sum*To see the number of counts of various colors at once, we will use Get statement.

**Count multiple Color cell using Get statement**

To create a robust way of counting color cell you will use Get. statement. Although it is a VBA Macro statement but does not require any coding per se. The GET.CELL function will extract the feature of a cell in this case **type of color**.

Simultaneously, we will use the Name Rage feature of Excel to create a formula to return the given color code.

In worksheet **GET**, all data points are the same as the previous example just the approach of counting the cell color is different.

The assignment is to count all three color cells at once.

**To count cell with multiple colors**

- Go to worksheet ‘
**GET**’ of Excel working file*(Image instructions below)* - Select Cell
**D5** - Click Formula>Name Manager
- Enter Name:
**ColorCode** - Enter the formula in
**Refers to**box: =GET.CELL(38,GET!C5) - Click
**OK** - Enter new formula ‘ColorCode’ in cell D5
- Now create
**=COUNTIF($D$5:$D$24,ColorCode)**formula to count the number of color cells

Now you have an easy way of counting multiple cells with different colors. It reduces the time to count each cell once a user set up the Excel file correctly.

Cell D5is a reference point of the formula do make necessary changes as required.

The **disadvantage** is that you have to refresh the formula each time after you make a change in the list.

- You have to save Excel as a
**Macro-Enabled**workbook - With a very large data set you may encounter Excel crash problem.
- Not applicable for conditional format cell