A Microsoft Excel forum. ExcelBanter

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 » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excel 2007: Can it SUMIF( ) using color as a condition ?



 
 
Thread Tools Display Modes
  #1  
Old December 19th 09, 08:20 AM posted to microsoft.public.excel.misc
Mr. Low[_2_]
external usenet poster
 
Posts: 1
Default 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  
Old December 19th 09, 11:52 AM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 8,521
Default Excel 2007: Can it SUMIF( ) using color as a condition ?

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

--
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  
Old January 13th 10, 01:15 PM posted to microsoft.public.excel.misc
Valy Greavu
external usenet poster
 
Posts: 3
Default 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...ful-solutions/

--
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  
Old January 13th 10, 01:20 PM posted to microsoft.public.excel.misc
Valy Greavu
external usenet poster
 
Posts: 3
Default Excel 2007: Can it SUMIF( ) using color as a condition ?

Try this:
http://valygreavu.wordpress.com/2010...ful-solutions/

--
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  
Old February 2nd 12, 03:44 AM posted to microsoft.public.excel.misc
Claude Ferron
external usenet poster
 
Posts: 2
Default There is no built-in 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 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
>>>
>>> --
>>> 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...ful-solutions/
>>>>
>>>> --
>>>> 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...ful-solutions/
>>>>>
>>>>> --
>>>>> Valy Greavu
>>>>> MCP, MOS Expert
>>>>>
>>>>>
>>>>> "Mr. Low" wrote:




  #6  
Old February 2nd 12, 03:46 AM posted to microsoft.public.excel.misc
Claude Ferron
external usenet poster
 
Posts: 2
Default There is no built-in 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 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
>>>
>>> --
>>> 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...ful-solutions/
>>>>
>>>> --
>>>> 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...ful-solutions/
>>>>>
>>>>> --
>>>>> 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  
Old February 2nd 12, 05:54 AM posted to microsoft.public.excel.misc
Gord Dibben[_2_]
external usenet poster
 
Posts: 614
Default There is no built-in 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 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
>>>>
>>>> --
>>>> 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...ful-solutions/
>>>>>
>>>>> --
>>>>> 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...ful-solutions/
>>>>>>
>>>>>> --
>>>>>> 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

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 On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 05:00 PM.


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