Sometimes we need to process data in Excel quickly by coloring them. We may want to count the number of colored cells or the values of the cells. Although VBA can be an option for this it is not so easy. Moreover, it can be very tricky to solve a problem by using VBA. Therefore here we are going to see 7 easy and efficient ways to sum colored cells in excel without vba.

## ๐**Download Excel File**

Download the free workbook for your practice:

**Learn to Sum Colored Cells in Excel Without VBA by These 7 Methods**

In order to demonstrate the ways to sum colored cells in Excel without VBA we are using a simple dataset of the mark sheet of students of a particular class. We are trying to know a student named Robinโs mark and entry number. 7 different ways to sum colored cells in excel without VBA are given in the following sections. The dataset is given below.

**Method**

### 1. Applying SUMIF Function

Here we are using the **SUMIF** function to calculate the total marks of Robin during three different terms. Therefore the step-by-step procedure is shown below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly here, we want to know the total mark of Robin in cell
**D13**.

- In the next step, we select the cell
**D13**and type the following formula:

**=SUMIF(B6:B11,โRobinโ,D6:D11)**

- Here
**SUMIF**function sums up all the data entries from column**D**if it matches the criteria of the name Robin from column**B.**

- Finally, we press
**Enter**and get the total mark of Robin.

**Method**

### 2. Using SUBTOTAL Function

Here we are using the **SUBTOTAL** ย function to again determine the total marks of Robin in three different terms in his class. So the step-by-step procedures are given below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly, we select our data range. Then we go to
**Insert**then select the**Tableย**option.

- Secondly, we will find a
**Create Table**dialogue box. We choose the**โMy table has headersโ**option and then press**OK**.

- In this step, we click on the
**filter arrow**at the**header**cell of column**Mark**and choose the**Filter by Color**option, and select our color.

- Finally, at this point, our table is filtered. At cell
**D13**, we write this formula in the formula bar to get our result.

**=SUBTOTAL(109, D5:D11)**

**๐จย ****Formula Breakdown**

๐ Here 109 is the** SUM** function which sums the value from **D5** to **D11**.

๐ Here **SUBTOTAL** function gives us the sum of all colored cells at column **D** in cell** D13**.

__Differences Between SUBTOTAL and SUM Functions__

There is a difference between the **SUBTOTAL** function and the **SUM** function even though both of them give us a summation of a data range. The **SUM** function only includes the filtered data while calculating the summation. But the **SUBTOTAL** function doesnโt include the filtered data of the data range. Clearly, it only takes the visible data into account.

**๐ Read More: 5 Effective Ways to Sum Random Cells in Excel**

**Method**

### 3. Applying FILTER Feature Based on Cell Color

Here we are going to use the **Filter** tool based on the color cell to determine the total marks of the colored cell mark, Robin. Therefore the step-by-step procedures are as follows.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly we select our data range. Then we go to the
**Sort and Filter**option and select**Filter**.

- After filtering our dataset we select the
**Mark**header then select**Filter by Color**and select our color.

- Finally, in this step at cell
**D13,**we insert this formula to get our result. Here**109**is basically the**SUMย**function.

**=SUBTOTAL(109, D5:D11)**

**๐จย ****Formula Breakdown**

๐ Here **109 **is the** SUM** function which sums the value from **D5** to **D11**.

๐ Here **SUBTOTAL** function gives us the sum of all colored cells at column **D** in cell** D13**.

**๐ Read More: How to Add Specific Cells in Excel (5 Easy Ways)**

**Method**

### 4. Applying FILTER Tool and SUM Function

Here we are going to use the** Filter **option and **SUM **together to determine the sum colored cells in excel without VBA. So the step-by-step procedures are as follows.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly here in cell
**D13,**we put this formula in the formula bar.

**=SUBTOTAL(103, B6:B11)**

**๐จย ****Formula Breakdown**

๐ Here **103 **is the** COUNTA **function which counts the entry from **B6** to **B11**.

๐ Here **SUBTOTAL** function gives us the sum of all colored cells entry at column **D** in cell** D13**.

- Finally, we again
**Filter**our dataset as shown in previous methods and get our result in cell**D13**.

**Method**

### 5. Using Find & Select Tool

Here we are going to learn how to use a feature called **FIND & SELECT** to determine the number of colored entries in a dataset. So the step-by-step procedures to sum colored cells in excel without VBA using **FIND & SELECT **are shown below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly, we select our data range. Then we go to the
**FIND & SELECT**option and select the**FIND**option.

- A dialogue box appears. Here we select the
**Formatย**option.

- Then we get the
**Find Format**dialogue box. We select the**Fill**option and choose our color and then select**OK**.

- Finally, we select
**Find All**All the colored cell entry results are shown below.

**๐ Read More: 4 Easy Ways to Add a Range of Cells in Excel**

**Method**

### 6. Applying GET.CELL Function

Here we are going to show how to use **GET.CELL** function to determine the total number of Robin which is denoted by colored cells. So the step-by-step procedures to sum colored cells in excel without VBA by areย given below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly, we go to the
**Formulas**Then we select**Name Manager**.

- Secondly, we get a dialogue box. We select the
**Newย**option.

- We give it the name
**ColorCode**. We put in the Refers to box the given formula.

**=GET.CELL(38,โMethod 6โ!$D6)**

**๐จย ****Formula Breakdown**

๐ย **38** returns the color code of the cells in column **D**.

๐ ย The name of our datasheet is **Method 6**.

๐ ย Column reference **D is **fixed by **!$D6ย **but the row remains unclocked.

- Here we get the color code of our entry by color in
**E6**.

- We use the
**Fill Handle**tool to drag the formula from**E6**to**E11**.

- Finally, in cell E13, we put the formula given below. Finally, we get our result in cell
**E13**.

**=SUMIF(E6:E11,ColorCode,D6:D11)**

**๐จย ****Formula Breakdown**

๐ Here **ColorCode** gives us color code return from **E6** to **E11**.

๐ **SUMIF **function sums all the entries of marks in column **D** if they have any color code value.

**๐ Read More: 5 Ways to SUM Last 5 Values in Row in Excel**

**Method**

### 7. Using GET.CELL and COUNTIF Functions

Here we are going to use **GET.CELL** and **COUNTIF** function to sum colored cells in Excel without VBA.So the step-by-step procedures are given below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- From the previous method, we again create the ColorCode. Then in
**D13,**we write this formula in the formula bar. We get our return value in cell**D13**after pressing**Enter**.

**=COUNTIF(E6:E11,ColorCode)**

**๐จย ****Formula Breakdown**

๐ **ColorCode** gives us color code return from **E6** to **E11**.

๐ **COUNTIF **function counts all the entries of marks in column **D** if they have any color code value.

__When Shouldnโt You Use GET.CELL?__

**GET.CELL** function is a great tool to sum colored cells in Excel without VBA. Therefore we must use it carefully. We canโt directly access the function when we write the formula. We have to define a custom function at first. Also, **GET.CELL** doesnโt work if we apply background color using conditional formatting.

**How to Count Colored Cells in Excel Using Formula**

Here we are going to see how to count colored cells in Excel using a formula. For example, we are coloring the cell in red. That means we are counting cells using the formula if the cell color is red. So the step-by-step procedures are given below.

**โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ**

- Firstly, we are putting this function in cell
**D13**.

**=SUBTOTAL(103, D6:D11)**

**๐จย ****Formula Breakdown**

๐ In here 103 is the** COUNTA **function which counts the entry from **D6** to **D11**.

๐ **SUBTOTAL** function gives us the count of all cell entries at column **D** in cell** D13**.

- Secondly, we select our data range. Then we go to the
**Editing**option and select**Sort & Filter**menu then we will select the**Filter**option.

- Here at cell
**B5**we select the dropdown option and choose**Filter by Color**. Then, we select the color red.

- Finally, we will see in column
**B**all the red-colored cells and in cell**D13**we get the sum of the total red-colored entry.

**๐ Read More: 3 Quick Approaches to Sum Top N Values in Excel**

**๐ Important Notes**

๐๏ธย Firstly, we have to save the Excel file as a Macro Enabled workbook when we use **GET.CELL** function.

๐๏ธย Finally, we have to fix the cell reference properly.

**๐ย Takeaways from This Article**

๐ย ย We can use 7 different ways to sum colored cells in Excel without VBA.

๐ย ย Also by using **GET.CELL** function we can create our own custom-made function.

**Conclusion**

Here to sum colored cells in excel without VBA we learned 7 different methods. We can use **SUMIF**, **SUBTOTAL**, **FILTER**, **FIND AND SELECT** or **GET.CELL** function. We can use whatever you like. Also, readers can comment in the comment section if they have any queries. The writer will try their best to come up with a solution. Last but not least follow **Excelden** for more Excel-related queries.