Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use sumbycolor fuction?
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.............................................. .................. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use sumbycolor fuction?
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 On Tue, 29 Nov 2005 09:50:24 -0800, Ghauri wrote: 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.............................................. .................. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to use sumbycolor fuction?
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 "Ghauri" wrote in message ... 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.............................................. .................. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|