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 insertVBA Editor Office
  5. Then click on Module
  6. And paste the code in module editor, which you have copied in step 1VBA editor in excel
  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:

COUNT OF COLOR EXCEL

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.

Comments

  1. By mark

    Reply

  2. By Robby

    Reply

  3. By prabhu

    Reply

  4. By Karen

    Reply

Leave a Reply