Excel Formula Based on Cell Color (5 Examples)

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.

Excel formula based on cell color : Sample dataset

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)

Count using SUBTOTAL for all quantities

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)

Sum using SUBTOTAL for all the quantities

  • Now, select the whole dataset.

Selecting dataset

  • From the Home tab, Select Filter in Sort & Filter drop-down menu.

Finding Filter from home tab

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

Arrow sign due to activating filter option

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

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

Result of Count for filtered dataset

Sum of the filtered dataset

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.

Finding Define Name from Formulas tab

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

Naming and Writing formula of GET.CELL

It will show in the Name Manager box.

  • If everything seems ok, then click Close.

Checking new name with formula

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

Result of color code using the NumberColor

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)

Count if for color code 36

In Cell G6,

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

COUNTIF result for color code 40

In Cell G7,

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

COUNTIF result for color code 55

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)

SUMIF result for color code 36

Similarly in Cell H6,

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

SUMIF result for color code 40

And, in Cell H7,

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

SUMIF result for color code 55

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

Result of IF formula by color code

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.

SUMIFS result for color codes

πŸ”Ž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.

Modified dataset for VBA macro application

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.

Selecting Module from insert tab for VBA macro Color code

  • The General window will open.

A general window for color code VBA macro

  • Copy and Paste the following code in the General window.

Code:

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

VBA macro code for color code

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

Color Index formula from VBA Code

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

COUNTIF result for VBA macro color code

  • Similarly, for applying SUMIF, write the formula given below in Cell F5:
=SUMIF($D$5:$D$10,$D5,$C$5:$C$10)

SUMIF result for VBA macro color code

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.

Insert tab for module selection for the VBA macro sum by color code

  • 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

VBA macro code for sum by color

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

Result of VBA macro sum by color

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.


Related Articles

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo