Need to learn how to sum up if cell color is red in Excel? Excel doesn’t have any built-in function, to sum up, if the cell color is red. However, a number of methods can manage to sum the cells according to their red color. If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through **4** easy and convenient methods to sum up if the cell color is red in Excel.

**Table of Contents**Expand

## If Cell Color Is Red Then Sum in Excel: 4 Methods

Here, we have a **Sales Report** of a certain fruit business. Columns **B**, **C**, **D**, and **E** represent **Sales Rep**, **Product Name**, **Status,** and **Sales** correspondingly.

In this case, rows containing the products which are **Not Delivered** are colored in red. Now, we’ll sum up the **Sales** amount of this red-colored cell. In other words, we’ll calculate the total sales amount of products that haven’t been delivered yet. So let’s explore them one by one.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience.

### 1. Using SUMIF Function to Sum If Cell Color Is Red in Excel

In our first method, we’ll use **the SUMIF function** to get our job done. Allow me to demonstrate the process step-by-step.

**📌**** Steps**

- At the very beginning, expand the data range by
**Column F**. - Then, write down
**Color**in cell**F4**as the heading of the column.

- At this moment, write down the background color name of rows in their corresponding cells in
**Column F**. - For example, in cell
**F5**, write down**White**. And, in cell**F7**, write down**Red**.

- After that, select cells in the
**B16:C17**range and create an output section in the selected area as shown in the image below.

*Note:** Here, we give red fill color in cell C16 because we’ll determine the Total Sales of red-colored cells in the E5:E14 range*.

- Later, select cell
**C17**. - Then, write down the following formula.

`=SUMIF(F5:F14,"Red",E5:E14)`

Here, **F5:F14** represents the range of the name of **Colors**. Besides, **E5:E14** serves as the range of the **Sales** amount.

**Formula Breakdown**

The

**SUMIF function**has three arguments. They are

**range**,

**criteria**,

**[sum range]**. Here, our

**range**is

**F5:F14**. This is the range of cells that we want to be evaluated by criteria.

And our

**criterion**is

**“Red”**which defines which cells will be added. Here, we used double quotes because

**Red**is a text string.

Also,

**E5:E14**is our

**[sum range]**. These are the actual cells to be summed up.

- Lastly, press
**ENTER**.

### 2. Employing GET.CELL Function to Sum If Cell Color Is Red in Excel

You can utilize the** GET.CELL function** along with the** SUMIF function** to sum up the colored cells in Excel. Now, observe how to combine them to sum up the red-colored cells. Just follow the steps below.

- Initially, select cell
**E5**. - Then, move to the
**Formulas**tab. - After that, click on the
**Defined Names**groups. - Later, select
**Define Name**from the drop-down menu.

- Astonishingly, the
**New Name**dialog box opens. - Then, write down
**SumRed**in the**Name**box. - Also, put down the following formula in the box of
**Refers to:**

`=GET.CELL(63,GET.CELL!$E5)`

**Formula Breakdown**

**GET.CELL(63,GET.CELL!$E5): 63**returns the fill (background) color of the cell.

**GET.CELL!**refers to the sheet name.

**$E5**is the cell address of the first cell to consider in

**Column E**.

- Next, click
**OK**.

- At this point, create a new column
**Color Code**in cells in the**F4:F14**range.

- Primarily, select cell
**F5**now and start to write the function name we just have created. - Surprisingly, you can see that the function name appears just after writing down
**=Su**in the cell. - Then, select the function
**SumRed**and press the**TAB**key on the keyboard. - Consistently, hit the
**ENTER**key.

- Hence, we input the function into cell
**F5**and got**0**as output. - So, it’s the color code of
**No Fill**background color.

- Later, drag the
**Fill Handle**icon to the end of the**Color Code**column.

Here, we can notice that cells with no background color have a color code of **0**. On the other hand, cells with red background color have a color code of **3**.

- Again, select cell
**C17**. - Also, write down the following formula.

`=SUMIF(F5:F14,3,E5:E14)`

Here, we are looking for cells, to sum up, in the **E5:E14** range with a color code of **3**.

- As always, press the
**ENTER**key.

### 3. Utilizing AutoFilter and SUBTOTAL Function

We can use the **AutoFilter** feature and **the SUBTOTAL function** too, to sum the red-colored cells in Excel. It’s simple & easy. Let’s see the process in detail.

**📌**** Steps**

- In the first place, select cells in the
**B4:E14**range. - Next, proceed to the
**Home**tab. - Then, click on the
**Editing**group. - After that, select the
**Sort & Filter**drop-down menu. - Lastly, choose
**Filter**from the drop-down list.

- Now, a down-head arrow is available beside each heading in the selected data range.

- At this time, click on the down-head arrow beside the
**Sales**heading. - Instantly, a context menu appears beside the icon.
- Then, tap on the
**Filter by Color**option. - Lastly, select the
**red color rectangle**under the section of**Filter by Cell Color**.

- Thus, we can see just the red-colored rows now. Other rows got hidden.

- In this instance, select cell
**C17**. - Then, get the following formula into the cell.

`=SUBTOTAL(109,E5:E14)`

Here, **109** is the **function_num** argument. It returns the sum without the hidden data. And, **E5:E14** is the **ref1** argument which is the range to apply the previous function.

- Ultimately, hit the
**ENTER**button.

Here, we’ve got just the sum of the visible cells. The hidden cells aren’t included in the calculation. Obviously, we can verify that. Just remember the **Total Sales** here.

- Again, click on the down-head arrow beside the
**Sales**heading. - Then, from the drop-down menu select
**Clear Filter From “Sales”**.

- Now, the hidden rows appear.
- Instantly, the
**Total Sales**amount manages to change in the blink of an eye. But, the formula remains unchanged.

### 4. Applying VBA Code

Have you ever thought of automating the same boring and repetitive steps in Excel? Think no more, because VBA has you covered. In fact, you can automate the prior method entirely with the help of **VBA**. So, without further delay, let’s dive in!

**📌**** Steps**

- To begin with, press the
**ALT**+**F11**key.

- Suddenly, the
**Microsoft Visual Basic for Applications**window will open. - Then, jump to the
**Insert**tab. - After that, select
**Module**from the options.

- It opens the code module where you need to paste the code below.

```
Function Sum_Red_Cells(cc As Range, rr As Range)
Dim x As Long
Dim y As Integer
y = cc.Interior.ColorIndex
For Each i In rr
If i.Interior.ColorIndex = y Then
x = WorksheetFunction.Sum(i, x)
End If
Next i
Sum_Red_Cells = x
End Function
```

- After that, return to the worksheet
**VBA**. - Then, select cell
**C17**and start to write the function name we just have created. - Surprisingly, you can see that the function name appears just after writing down
**=sum**in the cell. - Later, select the function
**Sum_Red_Cells**and press the**TAB**key on the keyboard.

- At this point, give the necessary arguments of the function.
**C16**is the cell reference for the red-colored cell.**E5:E14**is the cell range to perform the sum operation.

## How to Count Cells If Cell Color Is Red in Excel

To solve this problem, we are using the same dataset that we’ve used in the previous methods. Follow the steps below carefully.

**📌**** Steps**

- Firstly, repeat the steps of Method 2 to get the Color Code.

- Then, select cell
**C17**. - After that, get the following formula in the cell.

`=COUNTIF(F5:F14,SumRed)`

**The COUNTIF function** counts the number of total cells with the color code of **3** in the **F5:F14** range.

- Subsequently, press
**ENTER**.

Here, we got the output **5** as there is a total of **5** red cells in the **Sales** column.

## Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

**Download Practice Workbook**

You may download the following Excel workbook for better understanding and practice yourself.

## Conclusion

This article provides easy and brief solutions to sum up if the cell color is red in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

**Related Articles**

- How to Sum Colored Cells in Excel Without VBA
- How to Sum Random Cells in Excel
- How to Sum Visible Cells with Criteria in Excel
- How to Ignore Blank Cells in Excel Sum
- [Solved!] Currency Sum Not Working in Excel

**<< Go Back to Sum in Excel | Calculate in Excel | Learn Excel**