how to use sumbycolor fuction?

[Guest]

In my spread sheet I want to count the values of green, red and blue
background cells separetly, which are in a same row? i.e. I want excel to
only pick green cells and ignore others when counting the values. Does anyone
used this type of fuction before? I was thinking to use sumbycolor function,
but could not work it out.

Please help................................................................

 

[Gord Dibben]

Ghauri

Assuming you copied the sumbycolor code from Chip Pearson's site and placed it
in a general module of your workbook.............

http://www.cpearson.com/excel/colors.htm

Assuming your range of cells is A1:X1

In A4 enter =sumbycolor(A1:X1,number)

Where number is the index number of the the background color.

If you don't know the index number, go back to Chip's site and copy the code
for the CellColorIndex Function.

Place it in same module.

In A2 enter =cellcolorindex(A1) drag/copy across to X2

You will get a number for each color to use in your sumbycolor function.


Gord Dibben Excel MVP

 

[L. Howard Kittle]

Hi Ghauri,

Give this a look or adapt the code below to suit your sheet.

http://www.xldynamic.com/source/xld.ColourCounter.html


Sub SumColorCount()
Dim Orange46 As Integer, _
Red3 As Integer, _
Green4 As Integer
Dim Cell As Range

For Each Cell In Range("Data")
If Cell.Interior.ColorIndex = 46 Then
Orange46 = Orange46 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + Cell.Value
ElseIf Cell.Interior.ColorIndex = 4 Then
Green4 = Green4 + Cell.Value
End If
Next

Range("F10").Value = "Orange = " & Orange46
Range("F11").Value = "Red = " & Red3
Range("F12").Value = "Green = " & Green4

MsgBox " You have: " & vbCr _
& vbCr & " Orange " & Orange46 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4, _
vbOKOnly, "CountColor"

Range("F10").Value = ""
Range("F11").Value = ""
Range("F12").Value = ""
End Sub

Sub SumColorCountYellow()
Dim Yellow6 As Integer
Dim Cell As Range

For Each Cell In Range("DataY")
If Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + Cell.Value
End If
Next

Range("F1").Value = "Yellow = " & Yellow6

MsgBox " Yellow adds to " & Yellow6, _
vbOKOnly, "CountColor"

Range("F1").Value = ""

End Sub

HTH
Regards,
Howard

 

[mickeya7]

SumbyColor

To sum by color in Microsoft Excel you must create a Custom Function using VBA. Click on the link below to see how:

http://www.excelexchange.com/SumByColor.html