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

**Download Practice Workbook**

You can download the practice workbook from here.

**5 Examples of Excel Formula Based on Cell Color**

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

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 for counting the number of data and 109 to return the sum of the quantities.

📌 As reference both the formulas take a range of the 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 **formula 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

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

### 3. Excel IF Formula by Cell Color

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

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

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

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 is 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 differentiable 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.

**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 of the file has VBA Macro codes within it.

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