Tom’s Tutorials for Excel: Sum and Count Cells By Color

Tom’s Tutorials for Excel: Sum and Count Cells By Color

To sum, count, or perform a mathematical operation on a range of colored cells, you will need VBA. Excel does not have a built-in native worksheet function to mathematically evaluate contents of colored cells, such as summing the numbers in all red-colored cells. Note, this topic deals with cells whose actual pattern color was changed with the color palette, as opposed to color by Conditional Formatting which only changes the appearance of a cell’s color but really does not change the cell’s Interior ColorIndex property. If you want to count or sum cells that are colored by Conditional Formatting, a native worksheet function would do that by evaluating the formatted condition.

In the below picture for example, range A1:B10 has cells that are colored red, blue, yellow, and gray, and some cells have no color. The picture shows where three User Defined Functions (UDFs) have been entered into cells D2, F2, and H2 to demonstrate how you can sum or count colored cells.



The UDF in cell D2 sees the color of the cell it is in, and sums cells having that color.
The UDF in cell F2 evaluates for a color index number, for example #6 which is yellow.
The UDF in cell H2 is an example of how to return the count of all cells having any color.

From this point you can modify the UDFs’ code to average or subtotal for certain color(s), or to count only specific colors, or perform whatever math operation you wish.

Here are the three User Defined Functions that are being utilized by cells D2, F2, and H2 respectively. Comment notes are in green to help explain each code line.

Function SumCurrentColor(RangeToSum As Range) As Long
'Update the function when a cell is changed on the sheet.
Application.Volatile
'Declare the necessary variables.
Dim ColorID As Integer, ColorCell As Range, mySum As Double
'Identify the ColorID variable so you know what color to look for.
ColorID = Range(Application.Caller.Address).Interior.ColorIndex
'Loop through each cell in the range.
For Each ColorCell In RangeToSum
'If the cell’s color matches the color we are looking for,
'keep a running subtotal by adding the cell’s number value
'to the mySum variable.
If ColorCell.Interior.ColorIndex = ColorID Then mySum = mySum + ColorCell.Value
Next ColorCell
'The cells have all been evaluated, so you can define the
'SumCurrentColor function by setting it equal to the mySum variable.
SumCurrentColor = mySum
End Function

Function SumSingleColor(RangeToSum As Range, iColor As Integer) As Long
'Update the function when a cell is changed on the sheet.
Application.Volatile
'Declare the necessary variables.
Dim ColorCell As Range, mySum As Double
'Loop through each cell in the range.
For Each ColorCell In RangeToSum
'If the cell’s color matches the iColor color index variable, keep a
'running subtotal by adding the cell’s number value to the mySum variable.
If ColorCell.Interior.ColorIndex = iColor Then mySum = mySum + ColorCell.Value
Next ColorCell
'The cells have all been evaluated, so you can define the
'SumSingleColor function by setting it equal to the mySum variable.
SumSingleColor = mySum
End Function

Function CountColors(RangeToCount As Range) As Long
'Declare the necessary variables.
Dim ColorCell As Range, myCount As Double
'Loop through each cell in the range.
For Each ColorCell In RangeToCount
'If the cell has any color, add it to the running count.
If ColorCell.Interior.ColorIndex > 0 Then myCount = myCount + 1
Next ColorCell
'The cells have all been evaluated, so you can define the
'CountColors function by setting it equal to the myCount variable.
CountColors = myCount
End Function