Sum/Count by Cell Colour in Microsoft Excel (VBA)
Last week one of my friends asked me whether it is possible to add cells based on their cell colour or not.
As we know, Excel does not have such function/tool built in. However, it is possible to accomplish this using Visual Basic for Applications (VBA) in Excel.
Let’s go through an example. This example will show you how to Sum and Count cells based on the cell colour.
Click HERE to download the file.
Here we have a sample data in column A with random cells highlighted in Red, Yellow, and Green. We would like to Sum and Count all the Red cells, Yellow cells, and Green cells.
Go to Visual Basic for Applications by pressing ALT+F11 or Developer Tab > Visual Basic
(If you do not have a Developer Tab, go to Excel Options > Customize Ribbon > Check Developer > OK)
Once you are in the VBA, go to Insert, click Module, and copy/paste below code for SUM
Function SumByColour(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColour = cSum
End Function
Repeat the same steps for COUNT (VBA > Insert > Module)
Function CountByColour(CellColor As Range, CountRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex Then
CountByColour = CountByColour + 1
End If
Next TCell
End Function
Close the VBA and start the formulas in column E as shown below.
In the formula, we are telling Excel to look for certain colour in a specific range.
I hope you enjoyed this article.
Click HERE to download the file with answers.
