If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel 2003 had a function to "SumByColor" -- it's not in 2007?
Excel 2003 had a function to "SumByColor", I don't see that function in
Office 2007. Does anyone know a replacement, or how to do the same type of thing? |
#2
|
|||
|
|||
Excel 2003 had a function to "SumByColor" -- it's not in 2007?
That is not a standard function in Excel 2003, so you must have
downloaded some addin or code like this from Chip Pearson's site: http://www.cpearson.com/excel/colors.aspx Just download it again. Hope this helps. Pete On Nov 12, 5:11*pm, Becky wrote: Excel 2003 had a function to "SumByColor", I don't see that function in Office 2007. *Does anyone know a replacement, or how to do the same type of thing? |
#3
|
|||
|
|||
Excel 2003 had a function to "SumByColor" -- it's not in 2007?
Excel 2003 did not have any built-in functions to do this. You may have had
a user defined function or an add-in with a function that did this. See this: http://www.cpearson.com/Excel/colors.aspx Make sure you read the section titled: Color Change And Calculation -- Biff Microsoft Excel MVP "Becky" wrote in message ... Excel 2003 had a function to "SumByColor", I don't see that function in Office 2007. Does anyone know a replacement, or how to do the same type of thing? |
#4
|
|||
|
|||
Excel 2003 had a function to "SumByColor" -- it's not in 2007?
There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not recalculate if you change color. Every time you change the color you will need to recalculate or wait excel to recalculate... To install the UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. A1:A10 is the range to be checked. B1 is a lookup cell coloured .. =colorsum(A1:A10,B1) =colorcount(A1:A10,B1) 'function to sum values within colorred cells Function ColorSum(varRange As Range, varColor As Range) As Variant Dim varTemp As Variant, cell As Range ColorSum = 0 For Each cell In varRange If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then If IsNumeric(cell.Value) Then ColorSum = ColorSum + cell.Value End If Next End Function 'Function to count the colored cells (not colored through Conditional formatting) Function ColorCount(varRange As Range, varColor As Range) As Variant Dim varTemp As Variant, cell As Range ColorCount = 0 For Each cell In varRange If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then ColorCount = ColorCount + 1 End If Next End Function If this post helps click Yes --------------- Jacob Skaria "Becky" wrote: Excel 2003 had a function to "SumByColor", I don't see that function in Office 2007. Does anyone know a replacement, or how to do the same type of thing? |
Thread Tools | |
Display Modes | |
|
|