Sum by cell color

Last week I got a question from Mike:

Bastien,

Thank you for writing ASAP Utilities. It has saved me countless hours in Excel time and has given me capabilities that I wouldn't otherwise have. I wanted to ask for some help. I noticed that in the user-defined functions for ASAP Utilities there is a function "ASAPSumbyFontColor". I was wondering if you could provide me with the code for a user-defined function that would sum by cell color. I am an intermediate user of Excel with some macro experience but cannot figure out how to create this function.

Whatever help you could provide would be much appreciated.

Mike, good idea! I will adapt this new function in the next version. Untill then:

Here's the code:

Public Function ASAPSumByCellColor(rngSource As Range, _
                                   sColorIndex As Single) _
                                   As Single
  ' sums all cells within a range that have a certain
  ' background color (colorindex)
  Dim rngCel                                 As Range
  Application.Volatile
  N = 0
  For Each rngCel In rngSource
    If rngCel.Interior.ColorIndex = sColorIndex Then
      If IsNumeric(rngCel.Value) Then N = N + rngCel.Value
    End If
  Next
  ASAPSumByCellColor = N
End Function

2 Responses to “Sum by cell color”

  1. Firstly, thank you so much for asap utilities, I have downloaded several different peoples utilities, and paid for some of them, but the only one I regularly use is asap utilities.

    As for summing by colour, you might want to look at the ozgrid page on this. I use this method as do several others in my office, so it does work.
    http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

  2. I don't think anything better and more valuable could have been possible for an extensive excel user like me than ASAP utility
    Its been a great contribution to overall Excel users and me in particular

Pinterest