Can you use an IF function with cell color?

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:

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.

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.

Read More: Excel Cell Color: Add, Edit, Use & Remove

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:

• 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

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 (5 Ways)

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

Read More: How to Highlight Cell Using the If Statement in Excel (7 Ways)

Similar Readings

• How to Fill Cell with Color Based on Percentage in Excel (6 Methods)
• Highlight a Column in Excel (3 Methods)
• How to Highlight Cells Based on Text in Excel [2 Methods]
• Highlight a Cell in Excel (5 Methods)
• How to Highlight from Top to Bottom in Excel (5 Methods)

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.

Related Content: How to Highlight Cells in Excel Based on Value (9 Methods)

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:

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

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

• 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 (3 Easy Examples)

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.

Related Articles

• How to Highlight Selected Cells in Excel (5 Easy Ways)
• Highlight Selected Text in Excel (8 Ways)
• How to Compare Two Excel Sheets and Highlight Differences (7 Ways)
• Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)
• How to Highlight Every 5 Rows in Excel (4 Methods)