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.

**Table of Contents**hide

**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)`

**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`

**cell D5**into

**cell E5**.

- Use
**Fill Handle**to**AutoFill**data in**range E5:E13**.

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.

**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 followto do so.__Method 1__

- 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:
MsgBox "Not Found"
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:
MsgBox "Not Found"
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:
MsgBox "Not Found"
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:
MsgBox "Not Found"
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.

**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**).

**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.

**Frequently Asked Question**

**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.

**Download Practice Workbook**

You can download this practice workbook while going through this article.

**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

**How to Use Excel SUMIF with Blank Cells****How to Use SUMIF Function to Sum Not Blank Cells in Excel****Excel SUMIF Not Working****Sum If Greater Than and Less Than Cell Value in Excel****[Fixed!] Excel SUMIF with Wildcard Not Working****Excel SUMIF Function for Not Equal Criteria****Excel SUMIFS with Not Equal to Text Criteria**