A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel 2003 had a function to "SumByColor" -- it's not in 2007?



 
 
Thread Tools Display Modes
  #1  
Old November 12th, 2009, 06:11 PM posted to microsoft.public.excel.misc
Becky
external usenet poster
 
Posts: 230
Default 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  
Old November 12th, 2009, 06:23 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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  
Old November 12th, 2009, 06:27 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old November 12th, 2009, 06:35 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.