**Excel SUMIF function** calculates the sum of a range of cells based on specified criteria. It allows the users to specify a range of cells to be evaluated against criteria for determining which of those cells should be included in the sum, as well as putting a different range of cells to be used in the actual 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 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, but must be the same size as range. |
Optional |

For this example, consider a dataset that contains the names of the **products**, their **regions**, and their total **sales **amount. The cells containing the sales amount have different colors based on the product names. Those colors will be used for criteria in the SUMIF functions.

**Method 1 – Apply Excel SUMIF Function with Cell Color Code**

We can apply the Excel **SUMIF **function with cell color code as a criteria, which you can get via the **GET.CELL** function in **Name Manager**.

**Steps:**

- Select
**cell D5**and go to the**Formulas**tab, then choose**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:

`=GET.CELL(38,SUMIF_Color_Code!$D5)`

- Press
**OK**to confirm.

**GET.CELL**function will return an integer value that corresponds to the color of

**cell D14**of the

**SUMIF_Color_Code**sheet (the sheet’s name).

- 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 throughout the**range E5:E13**.

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

If the **GET.CELL** function is **BLOCKED** in your worksheet, you need to enable** Excel 4.0 macro** in your workbook:

- Go to
**Developer >> Macro Security >> Macro Settings.** **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

**Method 2 – Use Excel SUMIF Function with Cell Color Name**

For this method to work, you’ll need to manually input the color name.

**Steps:**

- 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

**Method 3 – Create a VBA Code to Get a Sum Based on Cell Color **

**Steps:**

- Get the color codes of the cells using the
**CELL**function from the**Name Manager**. You can followto do so.__Method 1__

**Right-click**on your worksheet and select**View Code**.

- Go to
**Insert,**then**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()`

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

```
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**E5 to E13**from the worksheet called**VBA**.**sumRng**and**cell_color**pick up values from the dialog**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 the message “
**Not Found**”.

**Method 4 – Use Excel VBA SUMIF to Create a UserForm and Get Sum Based on Cell Color**

This section applies 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,**then to**UserForm**.

- From the
**Toolbox**, you can choose different controls for your**UserForm**.

- Insert a few
**Labels**to write instructions. - Insert a
**RefEdit**that accepts a**Range**type data and a**TextBox**that allows a**String**type data for user input. - Put a
**CommandButton**to start processing.

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

- Run the
**UserForm**and fill in the text boxes to get the results.

__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 that the function will use for calculations.

` Set colorRng = Sheets("UserForm").Range("E5:E13")`

- This sets the
**colorRng**variable to the**range E5:E13**from the worksheet named**UserForm**.

` Set sumRng = Range(RefEdit1.Value)`

- This sets the
**sumRng**variable to the range selected by the user through the**RefEdit**in the form.

` cell_color = TextBox1.Value`

- This sets the
**cell_color**variable to the value entered by the user through the**TextBox**in the form.

` 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 produced by the form.

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

**Alternative to SUMIF – Use Filter Feature with SUBTOTAL Function to Sum Colored Cells in Excel**

**Steps:**

- Select
**cell D14**and paste the following formula:

`=SUBTOTAL(9,D5:D13)`

- Press
**Enter**and you will see the total sales of all the products.

**SUBTOTAL**function can be used 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)**of the calculation.

- Select the entire dataset and go to the
**Data**tab, then choose**Filter**.

- Press the
**drop-down**on the**Sales column**, then select the**Filter icon****.** - 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**

You will need to manually spell what colors the cells are in a separate column, like in the example.

**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 as an array; the sum of cells that are **Yellow **and the sum of cells that are **Green**.

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

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

**Steps:**

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

**Things to Remember**

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

- Use the correct color reference.
- If you don’t use the
**CELL.**functions, you’ll need to have separate cells that manually spell the name, or code them to fetch those values. - Use the
**$**sign properly for absolute cell referencing. - Specify the correct worksheet while applying
**VBA**codes.

**Frequently Asked Questions**

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

The ability to use the Excel **SUMIF **function as indicated in the samples above 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, but you need to use the same 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.

## Related Articles

- Sum If Greater Than and Less Than Cell Value in Excel
- How to Use Excel SUMIF with Greater Than Criterion
- How to Use 3D SUMIF for Multiple Worksheets in Excel

**<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel**