How to Sum Colored Cells in Excel (4 Ways)

sum colored cells in excel

There’re no built-in Excel functions that sum up the colored cells in Excel by themselves. Yet multiple ways can manage to sum up the cells based on their cell colors. In this blog post, you will learn 4 distinct ways, to sum up, the colored cells in Excel with easy examples and proper illustrations.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


4 Ways to Sum Colored Cells in Excel

We will be using a Product Price List data table to demonstrate all the methods, to sum up, colored cells in Excel.

Sum Colored Cells in Excel dataset

So, without having any further discussion let’s get into all the methods one by one.


1. Use of the SUMIF Function to Sum up Colored Cells in Excel

Suppose, you want to sum up the total price of the products having “MTT” in their product ids. To mark those products, you have attributed them with blue color. Now, we will discuss a formula that will sum up the values of the cells indicated by blue color. To do so, we can use the SUMIF function. Now follow the steps below to see how to do it.

🔗 Steps:

❶ First of all, add an extra column to specify the cell colors in column “Price”.

❷ Then select cell C16 ▶ to store the formula result.

❸ After that type

=SUMIF(E5:E13,"Blue",D5:D13)
within the cell.

❹ Finally press the ENTER button.

Use of the SUMIF Function to Sum up Colored Cells in Excel


2. Use of AutoFilter and SUBTOTAL to Add up Colored Cells in Excel

We can use the AutoFilter feature and the SUBTOTAL function too, to sum the colored cells in Excel. Here are the steps to follow:

🔗 Steps:

❶ First of all, select the whole data table.

❷ Then go to the Data ribbon.

❸ After that, click on the Filter command.

Use of AutoFilter and SUBTOTAL to Add up Colored Cells in Excel

❹ Now click on the dropdown icon at the corner of the Price column header.

❺ Then from the dropdown menu select Filter by Color.

❻ Then click on the blue color rectangle.

❼ Now select cell C16 ▶ to store the formula result.

Type

=SUBTOTAL(109,D5:D7)
within the cell.

❾ Finally finish the whole process by pressing the ENTER button.

That’s it.


Further Readings:


3. Use of Excel GET.CELL Function to Sum up Colored Cells

You can use the GET.CELL function along with the SUMIF function to sum up the colored cells in Excel. Now follows the steps below to see how to incorporate them together, to sum up, the colored cells.

🔗 Steps:

❶ First of all, go to FormulasDefined NamesName Manager.

Use of Excel GET.CELL Function to Sum up Colored Cells

Then Name Manager dialog box will pop up. From that box:

❷ Click on New.

After that, the Edit Name dialog box will pop up on the screen. From there,

❸ Assign a name, for example, Code within the Name bar.

Type the following code within the Refers to the bar.

=GET.CELL(38,GET.CELL!$D5)

❺ After that hit the OK button.

❻ Now you have to create a new column. For instance, the Code is as follows.

❼ Select cell E5 and type

=Code
within the cell and press the ENTER button.

❽ Now drag the Fill Handle icon to the end of the Code column.

❾ Now select cell C16 and enter the formula:

=SUMIF(E5:E13,33,D5:D13)

❿ Finally, terminate the process by pressing the ENTER button.

So, here comes the result!

␥  Formula Breakdown

  • =GET.CELL(38,GET.CELL!$D5) ▶ 38 refers the sum operation; GET.CELL! refers to the sheet name; $D5 is the cell address of the first colored cell.
  • =Code ▶ it’s a synthesized code as we have created in step 7.
  • =SUMIF(E5:E13,33,D5:D13) ▶ sums up the values of the cells in the Price column having color code 33.

4. Excel VBA Macros: Another Tool to Add up Colored Cells

You can also sum up the colored cells by using the VBA code. In this section, we will be creating a user-defined function using VBA, to sum up, the colored cells.

Now follow the steps below:

❶ First of all, press the ALT+F11 button to open the Excel VBA window.

❷ Now, go to the Insert ▶ Module.

❸ After the copy the following VBA code.

Function SumColoredCells(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumColoredCells = cSum
End Function

❹ Now paste and save this code in the VBA editor.

Excel VBA Macros: Another Tool to Add up Colored Cells

❺ Now select cell D16 ▶ to store the sum result.

❻ Enter the code within the cell:

=SumColoredCells($D$5,D5:D13)

This code will sum up all the cells indicated by yellow color.

❼ Finally, hit the ENTER button.

␥  Formula Breakdown

📌 Syntax =SumColoredCells(colored_cell,range)

  • $D$5 ▶ this is a sample colored cell filled with yellow color.
  • D5:D13 ▶ cell range to perform the sum operation.

📓 Note:

  • Formula to sum up the Blue painted cells:
=SumColoredCells($D$8,D6:D14)

Where cell $D$8 is a sample Blue painted cell.

  • Formula to sum up the Orange painted cells:
=SumColoredCells($D$11,D7:D15)

Where cell $D$11 is a sample Orange painted cell.


Things to Remember

📌 Be careful about the syntax of the functions.

📌 Insert the data ranges carefully into the formulas.


Conclusion

To wrap up, we have illustrated 4 different methods overall, to sum colored cells in Excel. Moreover, you can download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. Surely we will try to respond to all the relevant queries asap.


Read More

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo