Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Sum Colored Cells in Excel Without VBA (5 Ways)

Excel is a great tool for visualizing and analyzing data. While working with Excel sometimes it becomes necessary to sum the values of the colored cells or sum the number of colored cells quickly. In this article, we’ll demonstrate 5 convenient and quick methods to sum colored cells in Excel without VBA. So, let’s dive into the article to get to know the ways to do this job.

sum colored cells in excel without vba


Download Practice Workbook

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


5 Ways to Sum Colored Cells Without VBA in Excel

Here, I have a sales report for β€œX” Company. It concludes the Product, Region, and Sales in columns B, C, and D respectively. In this dataset, for Apples, the Sales are colored in Green.

sum colored cells in excel without vba

πŸ““ Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Using the following methods, you will be able to sum the Sales value based on this color or sum the number of Green colored cells in this data range.
Not to mention, for this purpose, we are using Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version


1. Using SUMIF Function

For getting the Total Sales of Apple based on its color you can use the SUMIF function. To do this task, I have added a column named Color.

πŸ“Œ Steps:

➀ First of all, write the color of cells of the Sales column manually in the Color column.

writing color name in newly created column

➀  Then, select the output cell E12.
➀  After that, type the following formula.

=SUMIF(E5:E11,"Green",D5:D11)

E5:E11 is the criteria_range, Green is the criteria and D5:D11 is the sum_range.
➀ Later, press ENTER.

Using SUMIF Function to sum colored cells in excel without vba

Result:
Now, you will get the Total Sales of Apple which is $8,863.

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)


2. Creating Table to Sum Values of Colored Cells

If you want to know the Total Sales of Apple based on its color, you can create a Table and use the SUBTOTAL function.

πŸ“Œ Steps:

➀ Firstly, select the data table (B4:D11).
➀ Next, go to Insert Tab>>Table Option
Then the Create Table dialog box will appear.
➀ Click the My table has headers option.
➀ As usual, press OK.

Creating Table to Sum Values of Colored Cells

After that, the table will be created.

Table created

➀ Then, click the Dropdown icon in the Sales column.
➀ Select the Filter by Color option.
➀ Hence, choose the green box as Filter by Cell Color.
➀ As always, press OK.

filter by color

Now, the table will be filtered by green.

only green cells are visible

➀ Select the output cell D12.
➀ Type the following formula.

=SUBTOTAL(109,D5:D9)

109 is for the SUM function, and D5:D9 is the range of cells containing Sales.
➀ Press ENTER.

SUBTOTAL function to sum colored cells in Excel without vba

Result:
Afterward, you will get the Total Sales of Apple which is $8,863.

Read More: How to Sum Filtered Cells in Excel (5 Suitable Ways)


3. Utilizing Filter Option to Sum Colored Cells

A Filter is a useful tool that helps us display only the specified values in Excel. Based on the filtered result, we can later edit, copy, chart, or print only the visible values. We can use this option to sum the values of colored cells in Excel too.


3.1 Sum Values of Colored Cells

You can have the Total Sales of Apple based on its color by using the Filter option and the SUBTOTAL function.

πŸ“Œ Steps:

➀ Firstly, select the output cell D12.
➀ Later, type the following formula

=SUBTOTAL(109,D5:D11)

109 is for the SUM function, and D5:D11 is the range of cells.
➀ Press ENTER.

SUBTOTAL function to sum colored cells in excel

Then, you will get the total Sales.

➀ Secondly, select the data range.
➀ Therefore, go to Data tab>>Sort & Filter dropdown>> Filter option.

Applying Filter

You can see filter buttons beside each column heading.

Filter Button in Excel

➀ Thirdly, click the Dropdown icon in the Sales column.
➀ Right now, select the Filter by Color option.
➀ Choose the green box as Filter by Cell Color.
➀ Then, press OK.

Filter by color to sum colored cells in excel without vba

Result:
Afterward, you will get the Total Sales of Apple which is $8,863.

Utilizing Filter Option to Sum Colored Cells in Excel

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)


3.2 Count of Colored Cells

If you want to know the sum of the number of Green colored cells or count the Green colored cells, you can use the Filter option and the SUBTOTAL function.

πŸ“Œ Steps:

➀ Initially, select the output cell D12.
➀ Type the following formula.

=SUBTOTAL(103,D5:D11)

103 is for the COUNTA function, and D5:D11 is the range of cells.
➀ Press ENTER.
Now, you will get the sum of the total number of cells.
➀ Follow the previous steps of Method-3.1.
Finally, you will get the sum of the number of Green colored cells.

Utilizing Filter Option to Sum Count of Colored Cells in Excel without VBA

Read More: Sum by Font Color in Excel (2 Effective Ways)


Similar Readings


4. Incorporating Find & Select Option

If you have the sum of the number of Green colored cells or have counted the Green colored cells, you can use the Find & Select option.

πŸ“Œ Steps:

➀ Primarily, select the data table (B4:D11).
➀ After that, go to Home tab>>Editing dropdown>>Find & Select dropdown>>Find option.

Incorporating Find & Select Option

After that, the Find and Replace dialog box will pop up.
➀ Secondarily, select the Format option.

Find and Replace dialog box in Excel

Then, the Find Format dialog box will appear
➀ Hence, select the Fill tab and choose the Green color.
➀ Press OK.

using Find Format dialog box to sum count of colored cells in excel

➀ Consequently, click Find All.

Find and Replace dialog box in Excel to sum colored cells

Result:
Then, you can see the total number of Green colored cells in the bottom corner of the dialog box, which indicates that there is a total of 3 green colored cells.

Count 3 colored cells in Excel worksheet

Read More: How to Sum Selected Cells in Excel (4 Easy Methods)


5. Applying GET.CELL Method

In this method, we are going to use an unconventional approach. Are you curious about it? Then see the following.


5.1 Sum Values of Colored Cells

You can use the GET.CELL function to sum up the Sales for Green colored cells.

πŸ“Œ Steps:

➀ Go to Formulas tab>>Defined Names dropdown>>Name Manager option.

Name Manager option

Then, the Name Manager Wizard will appear.
➀ Select the New option.

opening Name Manager dialog box

After that, the New Name dialog box will pop up.
➀ Type any type of name in the Name box, here I have used ClrCode.
➀ Select the Workbook option in the Scope box.
➀ Type the following formula in the Refers to box.

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

38 will return the Color Code and SUM!$D5 is the colored cell in the SUMIF sheet.
➀ Finally, press OK.

Applying GET.CELL Method to sum colored cells in excel

➀ Create a column named Code.

creating new column for color code in excel

➀ Type the following formula in the output cell E5.

=ClrCode

➀ Press ENTER.

ClrCode function

It is the function we created in the previous step, and it will return the Code of the colors.
➀ Double-click on the Fill Handle Tool.

using Fill Handle to sum colored cells in Excel

In this way, you will get the color codes for all of the cells.

color codes for cells in excel

➀ Select the output cell G5.
➀ Type the following formula.

=SUMIF(E5:E11,ClrCode,D5:D11)

E5:E11 is the criteria_range, ClrCode is the criteria and D5:D11 is the sum_range.

using SUMIF function to sum colored cells in excel without vba

Result:
Now, you will get the Total Sales of Apple which is $8,863.

πŸ““ Note: You have to save the Excel file as a Macro-enabled Workbook because of the GET.CELL function.


5.2 Count of Colored Cells

You can use the GET.CELL function to sum the number of Green colored cells.

πŸ“Œ Steps:

➀ Follow the previous steps of Method-5.1.
➀ Select the output cell G5.
➀ Type the following formula.

=COUNTIF(E5:E11,ClrCode)

E5:E11 is the criteria_range, and ClrCode is the criteria.

Applying GET.CELL Method to sum count of colored cells in Excel

Result:
After that, you will get the total number of Green colored cells in the range.

Read More: How to Sum Columns in Excel (7 Methods)


How to Sum Colored Cells in Excel with VBA

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, go to the Developer tab >> Visual Basic option.

opening Visual Basic from Developer tab ribbon

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

Inserting Module in VBE

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

VBA code to sum colored cells in excel

After that, navigate to the VBA worksheet β€œVBA”.
➀ Then, select cell D14 and start to write the function name we just created.
Surprisingly, you can see that the function name appears just after writing down =sum in the cell.
➀ Later, select the function Sum_Colored_Cells and press the TAB key on the keyboard.

calling Sum_Colored_Cells function

At this point, give the necessary arguments for the function. D13 is the cell reference for the green-colored cell. D5:D11 is the cell range to perform the sum operation.

Applying VBA to Sum Colored Cells in Excel


Practice Workbook

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 explains how to sum colored cells without VBA in Excel in a simple and concise manner. 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, a one-stop Excel solution provider, to explore more.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo