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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
4 comments on “Tom’s Tutorials for Excel: Sum and Count Cells By Color
  1. Taylor says:

    Tom,

    Great tutorial on summing or counting based on cell color. How would I subtotal based on cell color?

    Thank you.

    • Tom Urtis says:

      Thanks for following my Excel blog page. Can you give me some context on what you want to subtotal by? For example, in the picture, the red or the gray or the blue or the yellow cells would be subtotaled by definition of their cell color. In other words, using column A in my picture, the total of all numbers is 43. If only the red cells were totaled, that would essentially be a subtotal (9) of red cells in column A. I get the feeling you are looking for something more complex than that but I can’t tell what that would be from your question. Post back with more details and I’ll try to help as best I can for a solution you can use.

  2. Rajesh Sinha says:

    Tom it’s quite useful,, handy too. Is it possible to sum by colour using Excel Function?

    • Tom Urtis says:

      There’s no native function that can do this, for a cell’s interior color. Conditional Formatting is different, where you can sum and average based on the conditions changing the color.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

  • Facebook
  • Twitter
  • Instagram
  • Linkedin
  • Youtube