# How to Use Excel SUMIF Function Based on Cell Color

Get FREE Advanced Excel Exercises with Solutions!

Are you tired of manually sorting data in Excel to find and add up values based on cell color? Look no further than the Excel SUMIF function. This powerful function allows you to easily calculate summations based on specific criteria, including cell color. So, we can use the Excel SUMIF function with cell color to save valuable time. ## Explain Excel SUMIF Function

Excel SUMIF function calculates the sum of a range of cells based on one or more specified criteria. It allows the users to specify a range of cells to be summed as well as a separate range of cells to be used as criteria for determining which cells should be included in the sum.

The syntax of the Excel SUMIF function is as follows:

`SUMIF(range, criteria, [sum_range])` Arguments Explanation:

Parameter Data Type Description Requirement
range Range of Cells The range of cells that you want to evaluate based on criteria. The range can be a single column or row, or multiple columns or rows. Required
criteria Value or expression The criteria or condition that you want to apply to the range of cells. The criteria can be a value, a cell reference, or an expression that evaluates to a value or cell reference. Required
sum_range Range of cells The range of cells that you want to add up. If this argument is omitted, Excel will use the first range argument for the summing operation. The sum_range can be a single column or row, or multiple columns or rows. Optional

## Excel SUMIF Function Based on Cell Color: 4 Methods

In this guide, we will walk you through the steps to effectively utilize the Excel SUMIF function with cell color. We have demonstrated 4 easy methods to use the Excel SUMIF function with cell color.

Here in the dataset, we have the sales amount of different products. The dataset contains the names of the products, their regions, and their sales amount. The cells containing the sales amount have different colors based on the product. We will use the Excel SUMIF function with cell color to determine the total sales amount of different items. ### 1. Apply Excel SUMIF Function with Cell Color Code

We can apply the Excel SUMIF function with cell color code. To get the color code, we will use the GET.CELL function in Name Manager.

Steps:

• Select cell D5 and go to the Formulas tab >> Name Manager. • A new window will pop up named New Name.
• Insert Cell_Color_Code as the Name.
• Put this formula into the reference box and press OK:

`=GET.CELL(38,SUMIF_Color_Code!\$D5)` The GET.CELL function will return an integer value that corresponds to the color of cell D14 of the SUMIF_Color_Code sheet.
• Now close the Name Manager. • Put this formula in cell E5.
`=Cell_Color_Code` We get the corresponding color code of cell D5 into cell E5. We get the corresponding color code for all cells.

• Type this formula in cell E14.
`=SUMIF(E5:E13,6,D5:D13)`
• Press Enter and you will see the total sales amount of apples in cell E14. The SUMIF function will return the sum of specific cells in range D5:D13 that match the criteria (6) in the corresponding cells of range E5:E13.

Note: If the GET.CELL function is BLOCKED in your worksheet, you need to enable Excel 4.0 macro in your workbook. For that follow the instruction below:

• Go to Developer >> Macro Security >> Macro Settings and check the box that says Enable Excel 4.0 macros when VBA macros are enabled.
• Press OK to save changes.

Read More: How to Sum If Cell Contains Number and Text in Excel

### 2. Use Excel SUMIF Function with Cell Color Name

In this method, we will apply the Excel SUMIF function with the cell color name. We will find out the total sales amount of apples. So, we will sum up the cells of apple sales that are yellow. In the dataset, the cells in the Sales column have different colors. The names of the colors are mentioned in the corresponding cells of the Color column. • Select cell E14 and put in the following formula:
`=SUMIF(E5:E13,"Yellow",D5:D13)`
• Press Enter and you will see the total sales amount of apples. The SUMIF function will return the sum of specific cells from range D5:D13 that match the criteria (“Yellow”) in the corresponding cells of range E5:E13.

Read More: How to Sum If Cell Contains Number in Excel

### 3. Create a VBA Code to Get a Sum Based on Cell Color

We will apply a VBA code that uses the Excel SUMIF function to get a sum based on cell color.

• First, get the color codes of the cells using the CELL function from the Name Manager. You can follow Method 1 to do so. • You have to launch the VBA macro editor from your workbook. Right-click on your worksheet and select View Code.
• Go to Insert >> Module.
• Paste the following code in your VBA Editor and press the Run button or F5 key to run the code. ``````Sub sum_by_cell_color()
On Error GoTo Txt
'variable declaration
Dim colorRng As Range
Dim sumRng As Range
Dim cell_color, summa As Integer
'set variable values
Set colorRng = Sheets("VBA").Range("E5:E13")
Set sumRng = Application.InputBox("Select the Sales range:", Type:=8)
cell_color = InputBox("Insert the color code")
'use SUMIF function in colored cells
summa = Application.WorksheetFunction.SumIf(colorRng, cell_color, sumRng)
'show output
MsgBox "Total sales of the product: \$" & summa
Exit Sub
Txt:
End Sub``````

VBA Breakdown

``Sub sum_by_cell_color()``
• This is the start of a subroutine called sum_by_cell_color.
``On Error GoTo Txt``
• This is an error-handling statement that tells the program to jump to the Txt section if an error occurs.
``````    Dim colorRng As Range
Dim sumRng As Range
Dim cell_color, summa As Integer``````
• These are declared variables. colorRng and sumRng are Range type variables whereas cell_color and summa are Integer type variables.
``````    Set colorRng = Sheets("VBA").Range("E5:E13")
Set sumRng = Application.InputBox("Select the Sales range:", Type:=8)
cell_color = InputBox("Insert the color code")``````
• These commands set the values of some of the variables. colorRng takes values from the worksheet called VBA. sumRng and cell_color pick up values from InputBox.
``    summa = Application.WorksheetFunction.SumIf(colorRng, cell_color, sumRng)``
• This uses the SUMIF function from the worksheet to sum the values in the sumRng range that have the same background color as the cell_color value in the colorRng The result is stored in the summa variable.
``    MsgBox "Total sales of the product: \$" & summa``
• This MsgBox shows the output.
``    Exit Sub``
• This exits the subroutine.
``````Txt:
End Sub``````
• This is the error handling section that is triggered if any error occurs. It displays a MsgBox that shows the message “Not Found”.

Read More: How to Use Excel SUMIF with Greater Than Criterion

### 4. Use Excel VBA SUMIF to Create a UserForm and Get Sum Based on Cell Color

We will apply the following code in the UserForm that uses the Excel SUMIF function to get a sum based on cell color. Simply follow the steps below to create the UserForm:

• Go to Insert >> UserForm. • From the Toolbox, you can choose different controls for your UserForm. • We have inserted Labels to write instructions.
• We have inserted a RefEdit that accepts a Range type data and a TextBox that allows a String type data to take user input.
• We have also inserted a CommandButton to see the result. • Double-click on the CommandButton. A new window will open. Enter the following code:
``````Private Sub CommandButton1_Click()
On Error GoTo Txt
'variable declaration
Dim colorRng As Range
Dim sumRng As Range
Dim cell_color, summa As Integer
'set variable values
Set colorRng = Sheets("UserForm").Range("E5:E13")
Set sumRng = Range(RefEdit1.Value)
cell_color = TextBox1.Value
'use SUMIF function in colored cells
summa = Application.WorksheetFunction.SumIf(colorRng, cell_color, sumRng)
'show output
MsgBox "Total sales of the product: \$" & summa
Exit Sub
Txt:
End Sub``````

VBA Breakdown

``Private Sub CommandButton1_Click()``
• This is the start of a subroutine called CommandButton1_Click. This subroutine is executed when the user clicks on the SUBMIT button on the UserForm.
``On Error GoTo Txt``
• This is an error handling statement that tells the program to jump to the Txt section if an error occurs.
``````    Dim colorRng As Range
Dim sumRng As Range
Dim cell_color, summa As Integer``````
• These are declared variables. colorRng and sumRng are Range type variables whereas cell_color and summa are Integer type variables.
``    Set colorRng = Sheets("UserForm").Range("E5:E13")``
• This sets the colorRng variable to the range E5:E13 in the worksheet named UserForm.
``    Set sumRng = Range(RefEdit1.Value)``
• This sets the sumRng variable to the range selected by the user through the RefEdit
``    cell_color = TextBox1.Value``
• This sets the cell_color variable to the value entered by the user through the TextBox
``    summa = Application.WorksheetFunction.SumIf(colorRng, cell_color, sumRng)``
• This uses the SUMIF function from the worksheet to sum the values in the sumRng range that have the same background color as the cell_color value in the colorRng The result is stored in the summa variable.
``    MsgBox "Total sales of the product: \$" & summa``
• This MsgBox shows the output.
``    Exit Sub``
• This exits the subroutine.
``````Txt:
End Sub``````
• If any error occurs, it will trigger this error handling section. It displays a MsgBox that shows the message “Not Found

• Run the UserForm and follow the instructions shown in the video to see the output.

## Use Filter Feature with SUBTOTAL Function to Sum Colored Cells in Excel

We can use Filter feature along with the SUBTOTAL function to find the sum of cells of a particular color.

Steps:

• Select cell D14 and put the following formula.
`=SUBTOTAL(9,D5:D13)`
• Press Enter and you will see the total sales of all the products. We used the SUBTOTAL function to perform calculations on a range of cells that we want to filter or hide. The first argument “9” indicates that the function will perform the sum calculation. The second argument specifies the range D5:D13 to be included in the calculation.
• Select your dataset and go to the Data tab >> Filter. • Select the Filter icon on the Sales column.
• Click on Filter by color and select the Yellow color. You will see the total sales amount of apples. Read More: How to Use Excel SUMIF to Sum Values Greater Than 0

## Combine SUM and SUMIFS Functions to Sum All Colored Cells in Excel

We can combine SUM and SUMIFS functions to sum of all colored cells in Excel.

Steps:

• Select cell E14 and put the following formula:
`=SUM(SUMIFS(D5:D13,E5:E13,{"Yellow","Green"}))`
• Press Enter and you will see the summation of all colored cells (Yellow and Green). Note: Press Ctrl + Shift + Enter for older versions of Excel as it acts like an array formula.

Formula Breakdown

• SUMIFS(D5:D13,E5:E13,{“Yellow”,”Green”})

The SUMIFS function will sum the values from range D5:D13 that correspond to the rows where the values in range E5:E13 are either “Yellow” or “Green”. This array formula returns two values in consecutive cells; the sum of cells that are Yellow and the sum of cells that are Green.

Result: [ 14,244.00, 19,934.00]

• SUM(SUMIFS(D5:D13,E5:E13,{“Yellow”,”Green”}))

The SUM function takes the output of the SUMIFS function and returns the final sum of those values.

Result: 34,178.00

Alternative: You can also use the combination of SUM and SUMIF functions to sum all the colored cells. You may use the following formula.

`=SUM(SUMIF(E5:E13,"Yellow",D5:D13),SUMIF(E5:E13,"Green",D5:D13))`

Read More: How to Use SUMIF to SUM Less Than 0 in Excel

## How to Count Specific Colored Cells in Excel Using COUNTIF Function

In this section, we will apply Excel COUNTIF function to count the number of specific colored cells.

• Select cell E14 and put the following formula:
`=COUNTIF(E5:E13,"Yellow")`
• Press Enter and you will see the number of regions of apples (corresponding cell color is Yellow). The COUNTIF function returns the number of specific cells in range E5:E13 that match the criteria (“Yellow”).

Read More: How to Use 3D SUMIF for Multiple Worksheets in Excel

## Things to Remember

There are a few things to remember while using Excel SUMIF function based on cell color:

• Use the correct color reference.
• Use the \$ sign properly for absolute cell referencing.
• Specify the correct worksheet while applying VBA codes.

• Can I use the Excel SUMIF function based on cell color in all versions of Excel?

The ability to use the Excel SUMIF function based on cell color is available in Excel 2007 and later versions.

• Can I use the Excel SUMIF function based on cell color to sum values in cells with conditional formatting that is based on a formula?

Yes, you can use the Excel SUMIF function based on cell color to sum values in cells with conditional formatting that is based on a formula. However, you need to use the formula that is used in the conditional formatting as the criteria in the SUMIF function.

• Can I use the Excel SUMIF function based on cell color to sum values in cells with multiple colors?

No, the Excel SUMIF function based on cell color only works when the cells have one color. If the cells have multiple colors, the function will not work correctly.

## Conclusion

In this article, we have discussed 4 easy methods in detail to use Excel SUMIF function based on cell color. This article will allow users to use Excel more efficiently and effectively.  If you have any questions regarding this essay, feel free to let us know in the comments.

## Related Articles Md. Abu Sina Ibne Albaruni

This is Abu Sina, a fresh Engineering Graduate and currently an Excel and VBA content developer at SOFTEKO. I did my bachelor’s in Mechanical Engineering from BUET. My passion is to build new skills, gather experience and apply them to optimize and balance various trade-offs for a better future. Besides, I am fond of travelling, hanging out with friends, and watching movies.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  