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

• Tweet
• Email
• Sharebar

• Tweet
• Email

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.

Incoming search terms:

• colorfunction excel 2010
• how to get color count in excel 2010
• count colors in excel 2010
• sum color cells in excel
• excel sum colored cells 2007
• formula to count cell color in excel 2010
• how to count cell by color in excel 2010
• how to count by color in excel 2010
• how to count colored cells in excel 2007
• how to count cell colour in excel 2007

Tags

Related Posts

• 

  Internet Service Shared From Samsung Tab With A Computer

  You can allow the Wi-Fi mobile hotspot on your Samsung Galaxy Tab and divide up your device’s network connection by…
• 

  5 Office Gadgets That You Need But Don’t Even Know About Yet

  Most business owners have little time to spare, so it pays to have a few handy gadgets around the office….
• 

  How to Speed-up Your Internet Connection

  This is Guest Post by Sinep, Sinep is a writer from coupon blog, weightlosstriumph.com and loves sharing tips on how…

3 Comments

1. mark says:

   December 14, 2011 at 5:52 pm

   Great post. Love to see the full described post here. I love to use macro in Excel as I am a bit aware of VB.

   Reply
2. Robby says:

   May 4, 2012 at 3:58 am

   Is there a way to do this for Conditionally Formatted Cells?

   Reply
3. prabhu says:

   July 18, 2012 at 12:41 pm

   i need color function sum. i applied =colorfunction(e3,e1:i186,TRUE) its replayed #name?

   Reply