Tanjima Hossain
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.
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.
📓 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.
➤ 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.
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.
After that, the table will be 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.
Now, the table will be filtered by green.
➤ 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.
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.
Then, you will get the total Sales.
➤ Secondly, select the data range.
➤ Therefore, go to Data tab>>Sort & Filter dropdown>> Filter option.
You can see filter buttons beside each column heading.
➤ 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.
Result:
Afterward, you will get the Total Sales of Apple which is $8,863.
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.
Read More: Sum by Font Color in Excel (2 Effective Ways)
Similar Readings
- How to Sum by Group in Excel (4 Methods)
- Sum Only Visible Cells in Excel (4 Quick Ways)
- How to Sum Only Positive Numbers in Excel (4 Simple Ways)
- [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
- How to Calculate Cumulative Sum in Excel (9 Methods)
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.
After that, the Find and Replace dialog box will pop up.
➤ Secondarily, select the Format option.
Then, the Find Format dialog box will appear
➤ Hence, select the Fill tab and choose the Green color.
➤ Press OK.
➤ Consequently, click Find All.
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.
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.
Then, the Name Manager Wizard will appear.
➤ Select the New option.
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.
➤ Create a column named Code.
➤ Type the following formula in the output cell E5.
=ClrCode➤ Press ENTER.
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.
In this way, you will get the color codes for all of the cells.
➤ 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.
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.
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.
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, 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.
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.
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.
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.
