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.


Download Practice Workbook

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


4 Methods to Sum If Cell Color Is Red in Excel

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.

Excel if Cell Color Is Red then Sum

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.

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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.

Excel if Cell Color Is Red then Sum

Read More: If Cell Color Is Red Then Execute Different Functions in Excel


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.

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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.

Excel if Cell Color Is Red then Sum

Read More: Excel Formula to Change Text Color Based on Value (+ Bonus Methods)


Similar Readings


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.

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Excel if Cell Color Is Red then Sum

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

Using SUBTOTAL Function

Read More: Excel Conditional Formatting Formula


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.

Applying VBA Code

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

Applying VBA Code

  • 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

Applying VBA Code

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

Applying VBA Code

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

Applying VBA Code

Read More: VBA Conditional Formatting Based on Another Cell Value in Excel


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.

Using SUMIF Function

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

Using SUMIF Function

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

Read More: How to Change Text Color with Formula in Excel (2 Methods)


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.

Practice Section


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. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo