How to Sum in Excel If the Cell Color Is Red (4 Easy Methods)

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.

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.

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

• 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.

📌 Steps

• 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:

• 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.

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.

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.

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