In Excel, there is no function to make calculations based on the color of the cells. But by using Microsoft Visual Basic for Applications (VBA) it is possible to make custom functions for calculation based on the color of the cells. In this article, I will show you how you can create and use the ColorFunction in Excel.
Let’s say, we have a dataset of the product order of a company. The column Order Quantity is colored based on their delivery status. Delivered orders are colored with light green and orders which are in delivery are colored with light orange. Now we can find the number of customers who either received or didn’t receive their orders by counting the same colored cells. The total quantity of order delivered or order in delivery can be found by adding up the same colored cells.
Download Practice Workbook
ColorFunction in Excel
Step 1: Opening Macro Module in VBA window
First, you have to open the VBA window by pressing ALT+F11. After that right click on the sheet name from the left panel and go to Insert> Module
As a result, a Module box will be opened.
Step 2: Inserting VBA code for creating ColorFunction
Insert the following code, in the Module box and close the VBA window.
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function
Here, the VBA code creates a custom function named ColorFunction which will count colored cells if we give the argument as FALSE and will sum up the colored cells if we give the argument as TRUE.
Step 3: Saving the workbook as Excel Macro Enabled Workbook
You have to save the Excel workbook in .xlsm format to save your custom function. First, go to File from the upper left corner of your Excel window.
After that, Select Save As.
Then select Excel Macro-enabled Workbook (*xlsm) and click on Save.
Step 4: Using ColorFunction to Count Colored cells
Now you can use your custom ColorFunction.
To get the count of light green cells, type the following formula in an empty cell,
Here, F5 = Criteria cell which color will be counted
$D$5:$D$11 = Range for count
FALSE indicates that the number of cells having the same color with the criteria cell will be counted
Press ENTER and you will get the count for light green cells.
In a similar manner, you can get the count for light orange cells.
Step 5: Using ColorFunction to Sum Colored Cells
To get the sum of light green color cells, type the following formula in an empty cell,
Here, F5 = Criteria cell which color will be summed
$D$5:$D$11 = Range for sum
TRUE indicates that the number of cells having the same color with the criteria cell will be added up.
you will get the sum of light green cells by pressing ENTER.
In a similar manner, you can get the sum of light orange cells.
Alternative Ways to Count and Sum Colored Cell
Instead of using ColorFunction, you can get the count of colored cells or sum up the colored cells in two different ways.
1. Using FILTER and SUBTOTAL Functions
You can get the count and sum of colored cells by using Filter and the SUBTOTAL function.
First, go to the Data tab and click on Filter.
After that downward arrow will appear beside your every column header. Click on the downward arrow beside the Order Quantity (Colored column), go to Filter by Color, and select the light green color.
Now you will only see light green colored data in this column. To get the count type the following formula,
Here 2 indicates that the cell will be counted and D5:D11 is the range of data.
After pressing ENTER you will get the count of light green color cells.
To get the sum of the colored cells, type the following formula,
Here 9 indicates that the cell will be added up and D5:D11 is the range of data
After pressing ENTER you will get the sum of light green color cells.
By changing the filter to light orange color, you can get the count and sum of orange colored cells.
2. Using GET.CELL Function
By using the GET.CELL function you can also count and sum up the colored cells.
First, go to the Formulas tab and select Define Name.
A New Name box will appear. Type a name such as Color in the Name Box. and Insert the formula in Refers to box and press OK.
Here 38 indicates that the formula will give the color code of the referred cell and ’GET CELL’!$D5 is the referred cell (the first cell after the column header of the colored column)
Now type =Color (Name you gave previously in the Define Names box) in the adjacent column of your colored column.
After pressing ENTER and dragging cell E5 to the end of your dataset, you will get the color codes of all of your colored cells of column E.
Now type the following formula to get the count of light green color cells
Here, $E$5:$E$11 is the range for count, and 50 is the color code of light green.
After pressing Enter you will get the count of green colored cells.
Type the following formula to get the sum of light green colored cells,
Here, E5:E11 is the criteria range, 50 indicates the color code of light green and D5:D11 is the sum range.
Press ENTER and you will get the sum of all green colored cells.
In a similar way, you can get the count and sum of light orange colored cells.
I hope after going through the Article now you can create and use ColorFunction in Excel. If you face any problem please leave a comment.