If you want to find out the sum If the cell color is green in Excel, then you have come to the right place. Here, we will walk you through 4 easy methods to do the task smoothly.
Here, you can see in the following picture that we have found out the Sum of the Green Colored Sales in cell D12. In the following article. we will describe how you can also do the task.
4 Easy Methods to Find Sum in Excel If Cell Color Is Green
In the following dataset, you can see the Month and Sales columns. Here, you can easily notice that the Sales column has several cells colored in Green. Next, using this dataset we will find the sum if the cell color is green. Here, we used Excel 365. You can use any available Excel version.
1. Apply SUMIF Function Using a Helper Column with Color Names Written Manually
In this method, we will use the SUMIF function to find out the sum if the cell color is green. Here, we have added a Color column to our dataset. Next, we will use the SUMIF function to find out the sum of the Green color Sales.
Steps:
- First of all, we will type the following formula in cell C12.
=SUMIF(D5:D11,"Green",C5:C11)
Formula Breakdown:
- SUMIF(D5:D11, “Green”, C5:C11) → the SUMIF function finds the sum of a range of cells based on specified criteria.
- D5:D11 → is the range.
- Green → is the criteria.
- C5:C11 → is the sum_range.
Output: $4600
- After that, press ENTER.
- Therefore, you can see the result in cell C12.
Read More: How to Use If Statement Based on Cell Color in Excel
2. Use SUBTOTAL Function and Then Apply Filter Command to Filter Out Green Cells
In this method, we will use the SUBTOTAL function to find out the total Sales sum. Then, we will use the Filter feature to find the sum if the cell color is green.
Steps:
- In the first place, we will type the following formula in cell C12.
=SUBTOTAL(9,C5:C11)
Formula Breakdown:
- SUBTOTAL(9, C5:C11) → the SUBTOTAL function finds out the subtotal in a range of cells.
- 9 → indicates the function_num.
- C5:C11 → is ref_1.
Output: $4600
- After that, press ENTER.
- Therefore, you can see the result in cell C12.
- Afterward, to add the Filter icon to the column headings, we will select the column headings bu selecting cells B4:C4.
- After that, go to the Data tab >> select Filter.
- As a result, you can see the Filter icon in the column headings.
- Moreover, we will click on the Filter icon of the Sales >> select Filter by Color.
- After that, click on the green color.
Here, you can insert a Table and use the Filter by Color to filter the Green color and find the sum of these green-colored cells.
- As a result, you can see the Sum of the Green Colored Sales in cell D12.
Read More: How to Use Conditional Formatting If Statement Is Another Cell
3. Use GET.CELL Function to Get Color Code for Green and Then Sum Using SUMIF Function
In this method, we will use the GET.CELL function from Name Manager to find the sum if the cell color is green.
Steps:
- First of all, we will go to the Formulas tab >> select Name Manager.
- After that, a Name Manager dialog box will appear.
- Then, click on New.
- After that, a New Name dialog box will appear.
- Then, in the Name box, we will type color_code.
- In addition, in the Refers to box, we will type the following formula.
=GET.CELL(38, Sheet2!$C5)
- Afterward, click on Close.
- Furthermore, we will type the following formula in cell D5.
=color_code
- In addition press ENTER.
- As a result, you can see the result in cell D5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
- Hence, you can see the complete Color Code column.
- Next, we will type the following formula in cell D12.
=SUMIF(D5:D11,4,C5:C11)- Afterward, press ENTER.
- Therefore, you can see the result in cell D12.
Read More: SUMIF vs SUMIFS in ExcelÂ
4. Apply VBA Codes to Sum Green Cells Only
In this method, we will use VBA code to find the sum if the cell color is green in Excel. We will go through two ways to do tasks.
4.1. Get Color Code with VBA and Then Apply SUMIF Function to Get the Sum
Here, we will use the VBA function to find the sum if the cell color is green.
Steps:
- In the first place, go to the Developer tab >> select Visual Basic.
- This will bring out a VBA Editor window.
- Here, you can also press ALT+F11 to bring out the VBA Editor window.
- At this point, a VBA Editor window will appear.
- Then, from the Insert tab >> select Module.
- Furthermore, we will type the following code in the Module.
Function index_for_color(CellColor As Range)
index_for_color = CellColor.Interior.ColorIndex
End Function
- Next, Save the code >> go back to the Worksheet.
- Then, we will type the following formula in cell D5.
=index_for_color(C5)
- Furthermore, press ENTER.
- After that, you can see the index of the color Green in cell D5.
- Moreover, we will drag down the formula with the Fill Handle tool.
- Hence, you can see the complete Index for Color column.
- Afterward, to find the Sum of the Green Colored Sales, we will type the following formula in cell D12.
=SUMIF(D5:D11,4, C5:C11)
- In addition, press ENTER.
- Hence, you can see the result in cell D12.
Read More: How to Use 3D SUMIF for Multiple Worksheets in Excel
4.2. Create a VBA Function to Get Added Value for a Selected Color (Green)
Here, we will use the VBA code to get the summed-up value if the cell color is green in Excel.
Steps:
- First, we followed the steps of 5.1 to bring the Module.
- After that, we type the following code in the Module.
Function Sum_by_Green_Color(Cell_Color As Range, rRange As Range)
Dim c_sum As Double
Dim col_index As Integer
col_index = Cell_Color.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = col_index Then
c_sum = WorksheetFunction.Sum(cl, c_sum)
End If
Next cl
Sum_by_Green_Color = c_sum
End Function
- After that, we will go back to our Worksheet.
- Next, we will type the following formula in cell C13.
=Sum_by_Green_Color(C5,C5:C11)
- After that, press ENTER.
- Therefore, you can see the result in cell C13.
Read More: How to Sum Colored Cells in ExcelÂ
Practice Section
You can download the Excel file and practice the explained methods.
Download Practice Workbook
You can download the Excel file from the following link and practice while reading this article.
Conclusion
Here, we show you 4 easy methods for Excel if the cell color is green then sum. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.
Related Articles
- Use If Then Else Statement in Excel VBAÂ
- How to Ignore Blank Cells in Excel SumÂ
- Excel IF Statement Between Two NumbersÂ
- How to Prepare IF Statement Contains Multiple Words in Excel
- Use Wildcard with If Statement in ExcelÂ
- How to Use IF Function with OR and AND Statement in Excel
- Use Multiple IF Statements in Excel Data Validation
Thanks for the tutorial, now how do I sum the cells if the cell color is green, blue, and red?
Hello ZHENG,
Thank you for taking the time to read our article. If you have cells of different colors (such as green, blue, and red) and want to sum them up, the easiest way is to use the SUBTOTAL function with the Filter command. For example, let’s say you have sales values in the range C5:C11, where cells C5, C7, and C10 are green, C6 and C8 are blue, and C9 and C11 are red.
1. To get the sum of a definite colored cell, insert the formula in a cell:
=SUBTOTAL(9,C5:C11)Here, the SUBTOTAL function inserted in cell C12 uses 9 as the “function_name” argument (which refers to the SUM function) and C5:C11 as “ref1”.
2. Select the column header and go to the Data tab > Sort & Filter > Filter.
3. Click on the Filter icon of the column and select “Filter by Color.” You will see three different colors to choose from.
4. Select the color you want to sum up, and you will get the sum of all the cells with that color.
You can see the blue-colored cells filtered and summed up.
We hope this helps! Let us know if you have any further questions.
Regards,
Exceldemy Team