How to Calculate Sum and Count of Color Cells in Excel 2003, 2007 or 2010

Microsoft Office is most used tool by computer geeks and corporate persons to perform different tasks, i.e. Microsoft Outlook to send and receive emails, Microsoft Excel for data maintenance and calculation, Microsoft PowerPoint for slide presentation etc. We will talk about Microsoft Excel in this article, which has many features for data maintenance and calculation. It has many formulas to perform data calculation, has option of cell background color to highlight any particular data with color, but if we want to calculate Sum and Count of Color Cells in Excel 2003, 2007 or 2010 then you will not find any direct formula to Calculate Count and Sum of Color Cells in Excel 2003, 2007 or 2010.

Add VBA Macro in Microsoft Excel VBA Editor

1. First copy below mentioned code
   [code]Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
   Dim rCell As RangeDim lCol As LongDim vResultlCol = rColor.Interior.ColorIndexIf SUM = True ThenFor Each rCell In rRangeIf 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[/code]
2. Now Open your Microsoft Excel Sheet
3. Press Alt+F11 keys to open VBA Editor
4. Now click on insert
5. Then click on Module
6. And paste the code in module editor, which you have copied in step 1
7. Then save by pressing Ctrl+S keys or save it by File >> Save

Calculate COUNT of Color Cells by Background Color in Excel

1. Now you can use the formula “Colorfunction”
2. Just type below formula where you want to calculate count of color of a particular background color cells[code]=colorfunction(A,B:C,FALSE)[/code]
   • A denotes any cell no. with the particular background color you want to calculate the count
   • B:C denotes cell range where you want to calculate the count

Calculate SUM of Color Cells by Background Color in Excel

1. Now you can use the formula “Colorfunction”
2. Just type below formula where you want to calculate SUM of color of a particular background color cells[code]=colorfunction(A,B:C,TRUE)[/code]
   • A denotes any cell no. with the particular background color you want to calculate the SUM
   • B:C denotes cell range where you want to calculate the SUM

EXAMPLE:

In above figure you can see the sum and color of different colors with different formula used in Microsoft Excel. If you still have any query then share with us.