Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 88 Count or Sum Cells That Have a Specified Fill Color

Using a bit of code, you can easily
SUM or COUNT cells whose fill
color was specified manually.

Every
now and then, it's convenient to
SUM or COUNT cells that have a
specified fill color that you or another user have set manually, as
users often understand paint colors more readily than named ranges.
To do this, first open the workbook where you want to
COUNT or SUM cells by a fill
color. Go into the VBE by selecting Tools Macro
Visual Basic Editor (Alt/Option-F11) and then select Insert
Module to insert a standard module. In this module, type
the following code:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell) + vResult
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

Now
you can use the custom function ColorFunction in
formulas such as this:

=ColorFunction($C$1,$A$1:$A$12,TRUE)

to sum the values in the range of cells $A$1:$A$12
that have the same fill color as cell $C$1. The function will
sum in this example because you used
TRUE as the last argument for the custom function.

To
count the cells that have the same fill color as
cell $C$1, you can use this:

=ColorFunction($C$1,$A$1:$A$12,FALSE)

or:

=ColorFunction($C$1,$A$1:$A$12)

By omitting the last argument, the
function automatically defaults to using FALSE as
the last argument. Now you easily can SUM or
COUNT cells that have a specified fill color, as
shown in Figure 7-5.

Figure 7-5. Using the custom ColorFunction to count by fill color