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

Hi Low,

Unfortunately, Excel 2007 does not have the ability to use color as a condition in the SUMIF() function. However, there are a few workarounds that you can use to achieve the same result.

Option 1:

1. Use a helper column where you manually enter a value or text to indicate the color of the cell. For example, you could use "Red" for cells with red font color and "Green" for cells with green font color.
2. Then, you can use the SUMIF() function to sum up the values based on the helper column.

Option 2:

1. Use a macro to automate the process.
2. You can write a VBA code that loops through the cells and checks their font color or background color, and then adds up the values accordingly.

Let me know if you need any further assistance.

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

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

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

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:

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?

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?