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.

Contents

## Add VBA Macro in Microsoft Excel VBA Editor

- 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] - Now Open your Microsoft Excel Sheet
- Press Alt+F11 keys to open VBA Editor
- Now click on insert
- Then click on Module
- And paste the code in module editor, which you have copied in step 1
- Then save by pressing Ctrl+S keys or save it by
**File >> Save**

## Calculate COUNT of Color Cells by Background Color in Excel

- Now you can use the formula “Colorfunction”
- 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

- Now you can use the formula “Colorfunction”
- 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.

mark says

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.

Robby says

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

Robby says

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

prabhu says

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

prabhu says

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

Karen says

I created this macro, however if I change a color, it does not automatically change my total, I have to go into the formula and hit enter for it to pick it up. Any way to make it automatically change?

Karen says

I created this macro, however if I change a color, it does not automatically change my total, I have to go into the formula and hit enter for it to pick it up. Any way to make it automatically change?

David says

I have the same query as Karen above, does anyone have an answer to that?

David says

I have the same query as Karen above, does anyone have an answer to that?