Home 
Search 
Today's Posts 
#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 
#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? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
sumif with a second condition  Excel Worksheet Functions  
excel 2007  change fill color from one color to another  Excel Discussion (Misc queries)  
sumif with or< condition  Excel Worksheet Functions  
SUMIF function with text color condition  Excel Worksheet Functions  
sumif on more than one condition  Links and Linking in Excel 