Here’s an overview of applying formulas in cells based on the colors they have.

**Apply Formula Based on Cell Color in Excel: 5 Examples**

We will use the following colorful dataset to explain the methods. The dataset has two columns for Name and Quantity. There are 3 different colors in the rows, which we’ll use as criteria.

### Example 1 – Excel SUBTOTAL Formula with Cell Color

**Steps:**

- In Cell
**C6,**insert the following formula to get the Count of products in the list:

`=SUBTOTAL(102,C5:C10)`

- To get the Sum of the quantities of the product, use the following formula in
**Cell C14**:

`=SUBTOTAL(109,C5:C10)`

- Select the whole dataset.

- From the
**Home**tab, select**Filter**in the**Sort & Filter**drop-down menu.

- You will find two arrows in the columns of the dataset.

- Click on the arrow symbol of the column Name.
- Choose
**Filter by Color**. - Choose the color that you want to filter.

- Click
**OK**. - The values in Count by Color and Sum by Color will change.

- The results show the count and sum of only the filtered data.

**How Does the Formula Work?**

**SUBTOTAL**takes two arguments**function_name**and**ref1**. In the function_name it takes 102 to count the number of data and 109 to return the sum of the quantities. Specifically, 102 and 109 don’t count the cells (unlike functions 2 and 9, which do).- As a reference, both formulas take a range.
- When the filter is applied, some rows are hidden, so the formulas won’t account for them.

### Example 2 – Excel COUNTIF and SUMIF Formula by Cell Color

#### Case 2.1 – COUNTIF Formula with Cell Color

**Steps:**

- From the
**Formulas**tab, select**Define Name**.

- A box will appear. Write a name (in this case we wrote
**NumberColorÂ**in the**Name:**section). - In
**Refers to,**Â use the following formula:

`=GET.CELL(38,'2. COUNTIF and SUMIF'!$C14)`

- Click
**OK**.

- The name will show in the
**Name Manager**box. - Click
**Close**.

- Make a new column D for Color Code.
- In
**Cell D5,**apply the formula:

`=NumberColor`

- Press
**Enter**and drag the formula using the**Fill handle**icon to the rest of the column.

- You will get the code for all the colors present in the dataset.
- In
**G5,**insert this formula:

`=COUNTIF(D5:D10,$D$5)`

- In
**Cell G6**, insert the following:

`=COUNTIF(D5:D10,$D$6)`

- In
**Cell G7**, insert the following.

`=COUNTIF(D5:D10,$D$9)`

- For more accurate results, you should fetch the possible colors from an independent table (such as in column F) and use those values instead.

#### Case 2.2 – SUMIF Formula with Cell Color

**Steps:**

- Use the following formula in
**Cell H5**:

`=SUMIF(D5:D10,$D$5,C5:C10)`

- Insert the following in
**Cell H6**,

`=SUMIF(D5:D10,$D$6,C5:C10)`

- Insert the following formula in
**Cell H7:**

`=SUMIF(D5:D10,$D$9,C5:C10)`

**How Does the Process with Formulas Work?**

- The formula using the
**GET.CELL**function takes 38 to return code color and cell reference of which the code it will return. - Using the Color codes, we have applied the
**COUNTIF**and the**SUMIF**formula to get the count and sum of the data range with color code criteria.

**Read More:** How to Change Cell Color Based on a Value in Excel

### Example 3 – Excel IF Formula by Cell Color

We have the same price per piece for products like hoodies, jackets, and sweaters. We want to calculate the total price for the total quantities of these products.

**Steps:**

- We have created the NumberColor property using the Define Name and used it to find color codes (See Example 2).
- Insert this formula in
**Cell E5**:

`=IF(NumberColor=40,C5*$C$13,0)`

- Press
**Enter**. - Drag the
**Fill handle**icon to get the result for the rest of the data.

- The result showed values only for the products with the same color having color code 40 while zero (0) for the rest.

**How Does the Formula Work?**

- The
**IF**formula takes NumberColor to be equal to 40. - If the logic is true, it will multiply the quantity with the price per piece (5). Otherwise, it will show 0.

### Example 4 – Excel SUMIFS Formula by Cell Color

**Steps:**

- In
**Cell E5**insert the formula:

`=SUMIFS($C$5:$C$10,$D$5:$D$10,$D5)`

- Press
**Enter.** - Use the
**Fill handle**icon to drag the result for the rest of the cases.

**How Does the Formula Work?**

- The
**SUMIFS**formula takes the**sum_range****C5:C10**as absolute references for quantities. Followingly, it takes the color code range which is also in absolute reference form. - The criteria are set for the first cell of the color code column which is
**D5**. In this case, only the column is in absolute reference form while the rows are in relative reference form. It is because it will drag the**Fill handle**icon for the rest of the column by changing the row numbers as required.

### Example 5 – Excel VBA Macro to Use Excel Formula by Cell Color

The first sub-method will find the color code and then apply them to apply the **COUNTIF** and the **SUMIF** formulas.

**Note: VBA **Macro cannot recognize similar colors and so we modified our dataset with different colors.

#### Case 5.1 – VBA Macro to Find the Color Code

**Steps:**

- Press
**Alt + F11.** - This will open up the
**VBA**Macro window. Select your sheet. - From the
**Insert**tab, click on**Module**.

- The
**General**window will open.

- Copy and paste the following code in the General window.

**Code:**

```
Function ColorIndex(CellColor As Range)
ColorIndex = CellColor.Interior.ColorIndex
End Function
```

- Save the file as an Excel Macro-Enabled Workbook (.xlsm).
- Open your sheet and insert the following formula in Cell D5:

`=ColorIndex(C5)`

- Press
**Enter**and drag the**Fill handle**to get the result for the rest of the data.

- In
**Cell E5**, insert the formula below:

`=COUNTIF($D$5:$D$10,$D5)`

- Press
**Enter**and drag the formula down.

- Use the formula given below in
**Cell F5**:

`=SUMIF($D$5:$D$10,$D5,$C$5:$C$10)`

For this case, you have to find out the sum using color code. However, you can directly do the sum by writing a code. This will be explained in the next sub-method.

**How Does the Process with Formulas Work?**

- We have created ColorIndex using the code and keeping the argument as the range of the data. Using this we get the color codes.
- Next, we used the
**COUNTIF**formula to get the count result for that particular color code. - Lastly, we used the
**SUMIF**formula to get the sum based on the color code.

#### Case 5.2 – VBA Macro to Sum

**Steps:**

- Press
**Alt + F11**to open the**VBA**Macro Window. - Select your sheet and insert a
**Module**from the**Insert**tab.

- The
**General**window will open. Copy and paste the following code in the**General**window.

**Code:**

```
Function SBC(CClr As Range, rRng As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CClr.Interior.ColorIndex
For Each cl In rRng
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.SUM(cl, cSum)
End If
Next cl
SBC = cSum
End Function
```

- Open your worksheet.
- In
**Cell D5**, insert the following formula:

`=SBC($C5,$C$5:$C$10)`

- Press
**Enter**and drag the result using the**Fill handle**to the end of the data range.

You will get the result as shown in the above picture.

**How Does the Process with Formulas Work?**

- We created a formula with the name SBCÂ through the code we have written in the General window for this worksheet.
- After that, we used the formula with a range of data and criteria as the particular cell of quantities.

**Read More:** VBA to Change Cell Color Based on Value in Excel

**Things to Remember**

- Use different colors in case of applying
**VBA**Macro. - Save the Excel file with the .xlsm suffix in case the file has
**VBA**Macro codes within it.

**Download the Practice Workbook**

**Related Articles**

- Uses of CELL Color A1 in Excel
- Excel Formula to Change Cell Color Based on Text
- How to Fill Color in Cell Using Formula in Excel
- How to Fill Cell with Color Based on Percentage in Excel
- Excel Formula to Color Cell If It Has Specific Value
- How to Change Text Color with Formula in Excel
- How to Color Code Cells in Excel

**<< Go Back to Color Cell in Excel |Â Excel Cell Format |Â Learn Excel**

That’s a very nice article, just what I was looking for (count cells of a certain color) and more.

Hello,

Emil Lazar!Thanks for your appreciation. Stay in touch with

ExcelDemyto get more useful articles.Regards

ExcelDemyUsing this code, for cells where the quantity is less than one in each cell, I get a sum result = 0, even though the sum of the individual cells can be less than one, equal to 1, or greater than one.

Example

Cell A1 = .20

Cell B1 = .20

Cell C1 = .20

The sum of these cells is .60. The result after rounding up to the nearest integer would be 1.

How can I alter the code to show the the sum result to rounded up to the nearest integer?

Hello

Sia,To modify the VBA code to round up the sum result to the nearest integer, you can use the

Application.WorksheetFunction.Ceilingfunction. Use the following updated code to get your desired result:The formula will be :

=SumByColor(A1, A1:C1)Regards

ExcelDemy