How to Apply Formula Based on Cell Color in Excel (5 Easy Ways)

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.

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:

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:

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

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:

• 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:

• 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:

In Cell G6,

In Cell G7,

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:

Similarly in Cell H6,

And, in Cell H7,

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

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:

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

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:

• Afterward, press Enter.
• Use the Fill handle icon to drag the result for the rest of the cases.

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:

• 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:

• Press Enter and drag the result till the end of the data.

• Similarly, for applying SUMIF, write the formula given below in Cell F5:

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.

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:

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

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