MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Use Sum function when a cell is highlighted


Posted by Craig on July 03, 2001 10:44 AM

I would like to sum certain cells that are of a certain color. Can this be done fairly simply?


Posted by Damon Ostrander on July 03, 2001 12:17 PM

Hi Craig,

Yes, it can. Here is a simple VBA function that does that. This should be placed in a macro module in your workbook. Use it like SumIF, except in place of the condition provide the color index of the cells you want to sum. For example, the cell formula

=SumIFcolor(A5:J40,6)

will sum all the cells in the range A5:J40 that have color index 6 (yellow). The cells in the range must all have numeric values.

Happy summing.

Damon


Function SumIFcolor(InCells As Range, ColorIndex As Integer) As Double
SumIFcolor = 0
For Each c In InCells
If c.Interior.ColorIndex = ColorIndex Then
SumIFcolor = SumIFcolor + c.Value
End If
Next c
End Function

Posted by DA on July 03, 2001 1:08 PM

I may be wrong, but this may not work if the colors are occuring because of conditional formatting. If so, use a sumif statement tied to the same conditions as the formatting.

Good luck.

Posted by Craig on July 06, 2001 10:16 AM

Damon...I will give this a try however I have never used a macro module or VB but I had heard that in order to do what I wanted to do I would have to use VB...Thanks for help