You might have a colorful dataset and you would like to use the cell color to work with the Excel formula. Excel has a lot of amazing bunches of formulas to write and read data from datasets. Some of them are **COUNTIF**, **SUBTOTAL**,** IF**, and so on. Again, you can also use **VBA **macros to build new formulas according to requirements which you can apply for different cell colors. This article will explain 5 examples of Excel Formula based on cell color with proper illustrations.

**Table of Contents**Expand

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

We will use the following colorful dataset to explain the methods.

We can see that the dataset has two columns namely Name and Quantity. There are 3 different colors in the rows. We will be applying different Excel formulas like **SUMIF**, **SUBTOTAL**, **IF**, and user-defined functions using **VBA **macros in the 5 examples coming next. So, without any delay, let us jump into the main discussion.

### 1. Excel SUBTOTAL Formula with Cell Color

To apply the Excel **SUBTOTAL function** to count and get the sum of values filtered by color. Follow the steps below for this method.

**🚩Steps:**

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

`=SUBTOTAL(102,C5:C10)`

We can also use a **SUBTOTAL** formula for summation purposes. Let us see.

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

`=SUBTOTAL(109,C5:C10)`

- Now, select the whole dataset.

- From the
**Home**tab, Select**Filter**in**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.
- A sidebar drop-down menu will open. From there choose
**Filter by Color**. - Now, choose the color that you want to filter.

- Then click
**OK**.

It will show the filtered dataset.

You can notice the changes of values in Count by Color and Sum by Color in the pictures below.

Also, see the changes of Sum by Color in the pictures below.

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.- As reference both formulas take a range of quantities.
- The result at the start shows all the data in the range. However, the last two pictures show the result of filtered cells only.

### 2. Excel COUNTIF and SUMIF Formula by Cell Color

#### 2.1 COUNTIF Formula with Cell Color

Now, if you want to apply the **COUNTIF function **by cell color you need to follow the steps below.

**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:**write the following formula:

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

- After that, click
**OK**.

It will show in the **Name Manager** box.

- If everything seems ok, then click
**Close**.

- Besides the dataset take the column and in
**Cell D5**write the formula:

`=NumberColor`

- Press
**Enter**and drag this using the**Fill handle**icon to the rest of the columns.

You will get the code for all the colors present in the dataset.

- In a new cell, (
**G5**) write this formula:

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

In **Cell G6**,

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

In **Cell G7**,

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

You will see the result as shown in the pictures above. Anyway, you can also write the mixed or, relative cell reference of each of the cells in the formula and simply drag it down to get the results.

#### 2.2 SUMIF Formula with Cell Color

Now we’ll do it using **the SUMIF function**.

**Steps:**

Type the following formula in **Cell H5**:

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

Similarly in **Cell H6**,

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

And, in **Cell H7**,

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

Observe the pictures above to see how the results are found.

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

- Here, the formula using the
**GET.CELL**function takes 38 to return code color and cell reference of which the code it will return. - By defining Name for with the
**GET.CELL**formula we can simply write the name “NumberColor” prefixed by an equal sign will get the code of colors of the referenced cell. - Next, using the Color codes we have applied the
**COUNTIF**and the**SUMIF**formula to get the count and sum of data range with color code criteria.

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

### 3. Excel IF Formula by Cell Color

Now, let us say we have the same price per piece for products like hoodies, jackets, and sweaters.

If you want to calculate the total price for the total quantities of these products, we can use **the IF function**.

You can follow the steps to apply **IF** here.

**Steps:**

- We have already created NumberColor using Define Name and used it to find color codes (See method 2).
- In a new column, write the 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.

You can notice that it 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?**

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

### 4. Excel SUMIFS Formula by Cell Color

Using Color code, we can also apply the **SUMIFS function**.

For that, you need to follow the steps below:

**Steps:**

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

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

- Afterward, 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. - Lastly, 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.

### 5. Excel VBA Macro to Excel Formula by Cell Color

Moreover, **VBA **Macro can be an amazing tool to apply Excel formulas by cell color.

Let us subdivide this method into two parts for the convenience of understanding.

The first sub-method will use the code to 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.

The three different colors are red, blue, and brown. Now let us see how we can use **VBA **Macro to apply Excel formula by cell color.

#### 5.1 VBA Macro to Find Color Code

To find the color code using** VBA Macro** and apply the Excel formulas, we have to follow the steps below.

**Steps:**

- Press
**ALT+F11**from your keyboard. - 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 with Excel Macro-Enabled Workbook suffix.
- Open your sheet and write the following formula in Cell D5:

`=ColorIndex(C5)`

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

- Now, in another column at
**Cell E5**, you have to write the formula below:

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

- Press
**Enter**and drag the result till the end of the data.

- Similarly, for applying
**SUMIF,**write 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.

#### 5.2 VBA Macro to Sum

You have to follow the following steps to get the summation of the quantities of the same color directly through code.

**Steps:**

- You have to press
**ALT+F11**from your keyboard to open the**VBA**Macro Window. - Again, you have to select your sheet and From
**Module**from the**Insert**tab.

- Like the above sub-method, the
**General**window will open. Then just 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
```

- Next, open your worksheet. In
**Cell D5**, you have to write 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**

1. You have to use different colors in case of applying **VBA** Macro.

2. You have to save the Excel file with the .xlsm suffix in case the file has **VBA** Macro codes within it.

**Download Practice Workbook**

You can download the practice workbook from here.

**Conclusion**

The article explains 5 different methods to apply Excel formulas like **SUMIF**,** SUBTOTAL**, **COUNTIF**, and so forth based on cell color. Moreover, the practice workbook is there for you, so you can download it, and apply any of the methods as per your requirement. For any further queries, please write in the comment section.

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

ExcelDemy