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 2007: Can it SUMIF( ) using color as a condition ?
Hi,
I understand that Excel 2007 can sort by colors. It is a great feature indeed. May I know if =SUMIF( ) could use a color as the condition to sum up color shaded cells or cells with certain font color? Thanks Low 
Ads 
#2




Excel 2007: Can it SUMIF( ) using color as a condition ?
There is no builtin 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  Jacob "Mr. Low" wrote: > Hi, > > I understand that Excel 2007 can sort by colors. It is a great feature indeed. > > May I know if =SUMIF( ) could use a color as the condition to sum up color > shaded cells or cells with certain font color? > > Thanks > > Low 
#3




Excel 2007: Can it SUMIF( ) using color as a condition ?
You cannot do a native sumif by colors.
You must create a VBA function for that. Take a look he Function SumCuloare(Culoare As Range, Casute As Range) 'Definirea variabilelor Dim rrRange As Range Dim sumColor As Long Dim rrCasute As Range 'Definirea constantelor sumColor = 0 Set rrCasute = Casute vCuloare = Culoare.Font.Color ' Suma pe culori For Each rrRange In rrCasute If rrRange.Font.Color = vCuloare Then sumColor = sumColor + rrRange.Cells.Value End If Next rrRange ' Returnare rezultat SumCuloare = sumColor End Function You can see a sample on my blog: http://valygreavu.wordpress.com/2010...fulsolutions/  Valy Greavu MCP, MOS Expert "Mr. Low" wrote: > Hi, > > I understand that Excel 2007 can sort by colors. It is a great feature indeed. > > May I know if =SUMIF( ) could use a color as the condition to sum up color > shaded cells or cells with certain font color? > > Thanks > > Low 
#4




Excel 2007: Can it SUMIF( ) using color as a condition ?
Try this:
http://valygreavu.wordpress.com/2010...fulsolutions/  Valy Greavu MCP, MOS Expert "Mr. Low" wrote: > Hi, > > I understand that Excel 2007 can sort by colors. It is a great feature indeed. > > May I know if =SUMIF( ) could use a color as the condition to sum up color > shaded cells or cells with certain font color? > > Thanks > > Low 
#5




There is no builtin excel function to do this.
This code does not work if your cells are colored by conditions (conditional formatting) Anyway to do the same but with these kid of cells?
> On Saturday, December 19, 2009 3:20 AM Mr. Low wrote: > Hi, > > I understand that Excel 2007 can sort by colors. It is a great feature indeed. > > May I know if =SUMIF( ) could use a color as the condition to sum up color > shaded cells or cells with certain font color? > > Thanks > > Low >> On Saturday, December 19, 2009 4:18 AM ????? (????) ????? wrote: >> If I understood you correctly  you can type' into a Module, a small User >> Defined Function  such as: >>  >> Function IntColor(Rng As Range) >> Application.Volatile >> IntColor = Rng.Interior.ColorIndex >> End Function >> add >>  >> Assuming your list is in range A1:A10  in B1 type: =IntColor(A1)+NOW()*0 >> and copy down to B10 to get the ColorIndex in Range B1:B10.. >> Now, you can SUM the values according to the ColorIndex. >> Micky >> >> >> "Mr. Low" wrote: >>> On Saturday, December 19, 2009 6:52 AM Jacob Skaria wrote: >>> There is no builtin 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 >>> >>>  >>> Jacob >>> >>> >>> "Mr. Low" wrote: >>>> On Wednesday, January 13, 2010 8:15 AM Valy Greavu wrote: >>>> You cannot do a native sumif by colors. >>>> You must create a VBA function for that. >>>> >>>> Take a look he >>>> >>>> Function SumCuloare(Culoare As Range, Casute As Range) >>>> >>>> 'Definirea variabilelor >>>> Dim rrRange As Range >>>> Dim sumColor As Long >>>> Dim rrCasute As Range >>>> >>>> 'Definirea constantelor >>>> sumColor = 0 >>>> Set rrCasute = Casute >>>> vCuloare = Culoare.Font.Color >>>> >>>> ' Suma pe culori >>>> For Each rrRange In rrCasute >>>> If rrRange.Font.Color = vCuloare Then >>>> sumColor = sumColor + rrRange.Cells.Value >>>> End If >>>> Next rrRange >>>> >>>> ' Returnare rezultat >>>> SumCuloare = sumColor >>>> >>>> End Function >>>> >>>> >>>> You can see a sample on my blog: >>>> http://valygreavu.wordpress.com/2010...fulsolutions/ >>>> >>>>  >>>> Valy Greavu >>>> MCP, MOS Expert >>>> >>>> >>>> "Mr. Low" wrote: >>>>> On Wednesday, January 13, 2010 8:20 AM Valy Greavu wrote: >>>>> Try this: >>>>> http://valygreavu.wordpress.com/2010...fulsolutions/ >>>>> >>>>>  >>>>> Valy Greavu >>>>> MCP, MOS Expert >>>>> >>>>> >>>>> "Mr. Low" wrote: 
#6




There is no builtin excel function to do this.
The function works but...
With conditional formatting based colors, it sees the cells as it if had no colors at all Any ways to make this work with conditional formatting? > On Saturday, December 19, 2009 3:20 AM Mr. Low wrote: > Hi, > > I understand that Excel 2007 can sort by colors. It is a great feature indeed. > > May I know if =SUMIF( ) could use a color as the condition to sum up color > shaded cells or cells with certain font color? > > Thanks > > Low >> On Saturday, December 19, 2009 4:18 AM ????? (????) ????? wrote: >> If I understood you correctly  you can type' into a Module, a small User >> Defined Function  such as: >>  >> Function IntColor(Rng As Range) >> Application.Volatile >> IntColor = Rng.Interior.ColorIndex >> End Function >> add >>  >> Assuming your list is in range A1:A10  in B1 type: =IntColor(A1)+NOW()*0 >> and copy down to B10 to get the ColorIndex in Range B1:B10.. >> Now, you can SUM the values according to the ColorIndex. >> Micky >> >> >> "Mr. Low" wrote: >>> On Saturday, December 19, 2009 6:52 AM Jacob Skaria wrote: >>> There is no builtin 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 >>> >>>  >>> Jacob >>> >>> >>> "Mr. Low" wrote: >>>> On Wednesday, January 13, 2010 8:15 AM Valy Greavu wrote: >>>> You cannot do a native sumif by colors. >>>> You must create a VBA function for that. >>>> >>>> Take a look he >>>> >>>> Function SumCuloare(Culoare As Range, Casute As Range) >>>> >>>> 'Definirea variabilelor >>>> Dim rrRange As Range >>>> Dim sumColor As Long >>>> Dim rrCasute As Range >>>> >>>> 'Definirea constantelor >>>> sumColor = 0 >>>> Set rrCasute = Casute >>>> vCuloare = Culoare.Font.Color >>>> >>>> ' Suma pe culori >>>> For Each rrRange In rrCasute >>>> If rrRange.Font.Color = vCuloare Then >>>> sumColor = sumColor + rrRange.Cells.Value >>>> End If >>>> Next rrRange >>>> >>>> ' Returnare rezultat >>>> SumCuloare = sumColor >>>> >>>> End Function >>>> >>>> >>>> You can see a sample on my blog: >>>> http://valygreavu.wordpress.com/2010...fulsolutions/ >>>> >>>>  >>>> Valy Greavu >>>> MCP, MOS Expert >>>> >>>> >>>> "Mr. Low" wrote: >>>>> On Wednesday, January 13, 2010 8:20 AM Valy Greavu wrote: >>>>> Try this: >>>>> http://valygreavu.wordpress.com/2010...fulsolutions/ >>>>> >>>>>  >>>>> Valy Greavu >>>>> MCP, MOS Expert >>>>> >>>>> >>>>> "Mr. Low" wrote: >>>>>> On Wednesday, February 01, 2012 10:42 PM Claude Ferron wrote: >>>>>> This code does not work if your cells are colored by conditions (conditional formatting) Anyway to do the same but with these kid of cells? 
#7




There is no builtin excel function to do this.
See Chip Pearson's site
http://www.cpearson.com/excel/CFColors.htm Gord On Thu, 02 Feb 2012 03:46:04 GMT, Claude Ferron > wrote: >The function works but... > >With conditional formatting based colors, it sees the cells as it if had no colors at all Any ways to make this work with conditional formatting? > >> On Saturday, December 19, 2009 3:20 AM Mr. Low wrote: > >> Hi, >> >> I understand that Excel 2007 can sort by colors. It is a great feature indeed. >> >> May I know if =SUMIF( ) could use a color as the condition to sum up color >> shaded cells or cells with certain font color? >> >> Thanks >> >> Low > > >>> On Saturday, December 19, 2009 4:18 AM ????? (????) ????? wrote: > >>> If I understood you correctly  you can type' into a Module, a small User >>> Defined Function  such as: >>>  >>> Function IntColor(Rng As Range) >>> Application.Volatile >>> IntColor = Rng.Interior.ColorIndex >>> End Function >>> add >>>  >>> Assuming your list is in range A1:A10  in B1 type: =IntColor(A1)+NOW()*0 >>> and copy down to B10 to get the ColorIndex in Range B1:B10.. >>> Now, you can SUM the values according to the ColorIndex. >>> Micky >>> >>> >>> "Mr. Low" wrote: > > >>>> On Saturday, December 19, 2009 6:52 AM Jacob Skaria wrote: > >>>> There is no builtin 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 >>>> >>>>  >>>> Jacob >>>> >>>> >>>> "Mr. Low" wrote: > > >>>>> On Wednesday, January 13, 2010 8:15 AM Valy Greavu wrote: > >>>>> You cannot do a native sumif by colors. >>>>> You must create a VBA function for that. >>>>> >>>>> Take a look he >>>>> >>>>> Function SumCuloare(Culoare As Range, Casute As Range) >>>>> >>>>> 'Definirea variabilelor >>>>> Dim rrRange As Range >>>>> Dim sumColor As Long >>>>> Dim rrCasute As Range >>>>> >>>>> 'Definirea constantelor >>>>> sumColor = 0 >>>>> Set rrCasute = Casute >>>>> vCuloare = Culoare.Font.Color >>>>> >>>>> ' Suma pe culori >>>>> For Each rrRange In rrCasute >>>>> If rrRange.Font.Color = vCuloare Then >>>>> sumColor = sumColor + rrRange.Cells.Value >>>>> End If >>>>> Next rrRange >>>>> >>>>> ' Returnare rezultat >>>>> SumCuloare = sumColor >>>>> >>>>> End Function >>>>> >>>>> >>>>> You can see a sample on my blog: >>>>> http://valygreavu.wordpress.com/2010...fulsolutions/ >>>>> >>>>>  >>>>> Valy Greavu >>>>> MCP, MOS Expert >>>>> >>>>> >>>>> "Mr. Low" wrote: > > >>>>>> On Wednesday, January 13, 2010 8:20 AM Valy Greavu wrote: > >>>>>> Try this: >>>>>> http://valygreavu.wordpress.com/2010...fulsolutions/ >>>>>> >>>>>>  >>>>>> Valy Greavu >>>>>> MCP, MOS Expert >>>>>> >>>>>> >>>>>> "Mr. Low" wrote: > > >>>>>>> On Wednesday, February 01, 2012 10:42 PM Claude Ferron wrote: > >>>>>>> This code does not work if your cells are colored by conditions (conditional formatting) Anyway to do the same but with these kid of cells? > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
sumif with a second condition  jewel  Excel Worksheet Functions  6  September 5th 08 07:46 PM 
excel 2007  change fill color from one color to another  okiedokiedoo  Excel Discussion (Misc queries)  1  December 30th 07 10:25 PM 
sumif with >or< condition  Will Fleenor  Excel Worksheet Functions  8  April 27th 07 07:45 AM 
SUMIF function with text color condition  MrWallyWorld  Excel Worksheet Functions  4  August 18th 06 04:05 AM 
sumif on more than one condition  steve alcock  Links and Linking in Excel  4  May 13th 05 01:53 PM 