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

   

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

figs/moderate.gif figs/hack88.gif

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,$A:$A,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,$A:$A,FALSE) 

or:

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

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
figs/exhk_0705.gif


Excel Hacks
Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets
ISBN: 0596528345
EAN: 2147483647
Year: 2003
Pages: 136

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net