Conditional Sum
 

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

thanks for your reply but it is more difficult to call this formula with
colour number as I use several colours in my sheet could I use the colour
name instead

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

Sorry for inconvenience but the code is also not working & give result 0 for
every color, please advise.

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

Hi,

Try this instead. call with

=SumColour(A1:A15,"Yellow") or Red etc

Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Interior.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

thanks for your reply but it is more difficult to call this formula with
colour number as I use several colours in my sheet could I use the colour
name instead

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

Dear Mike

your code works good with fill colors but I want that it work with font
colors not with fill colors
--

Regards,



"Mike H" wrote:

Hi,

Try this instead. call with

=SumColour(A1:A15,"Yellow") or Red etc

Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Interior.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

thanks for your reply but it is more difficult to call this formula with
colour number as I use several colours in my sheet could I use the colour
name instead

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

I slightly change your code & it work great is it right???
Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Font.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

--
--
Regards,



"Mike H" wrote:

Hi,

Try this instead. call with

=SumColour(A1:A15,"Yellow") or Red etc

Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Interior.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

thanks for your reply but it is more difficult to call this formula with
colour number as I use several colours in my sheet could I use the colour
name instead

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

Ammended to work for font colour

Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Font.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

Dear Mike

your code works good with fill colors but I want that it work with font
colors not with fill colors
--

Regards,



"Mike H" wrote:

Hi,

Try this instead. call with

=SumColour(A1:A15,"Yellow") or Red etc

Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Interior.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

thanks for your reply but it is more difficult to call this formula with
colour number as I use several colours in my sheet could I use the colour
name instead

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??

Conditional Sum
 

Exactly the same change I made in my last post, Now carry on and add as many
colours as you require but note that in the code the colour must be in UPPER
CASE.

Mike

"Abdul Shakeel" wrote:

I slightly change your code & it work great is it right???
Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Font.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

--
--
Regards,



"Mike H" wrote:

Hi,

Try this instead. call with

=SumColour(A1:A15,"Yellow") or Red etc

Function SumColour(r As Range, iCol As String) As Long
Select Case UCase(iCol)
Case Is = "YELLOW"
cindex = 6
Case Is = "BLUE"
cindex = 5
Case Is = "GREEN"
cindex = 4
Case Is = "RED"
cindex = 3
Case Is = "BROWN"
cindex = 53
Case Else
End Select
For Each c In r
If c.Interior.ColorIndex = cindex And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

thanks for your reply but it is more difficult to call this formula with
colour number as I use several colours in my sheet could I use the colour
name instead

--
Regards,



"Mike H" wrote:

Hi,

You need a UDF for that. Try this one but note it doesn't work for
conditional format colours, that requires a different and much more
complicated method. Alt + F11 to open VB editor. Right click 'This workbook'
and insert module and paste ths code below in on the right.

Call with

=SumColour(A1:A20,6)
6 is yellow and if your not sure of the colours record a macro of yourself
colouring the cells to get the numbers.

Function SumColour(r As Range, iCol As Integer) As Long
For Each c In r
If c.Interior.ColorIndex = iCol And _
IsNumeric(c) Then
SumColour = SumColour + c.Value
End If
Next c
End Function

Mike

"Abdul Shakeel" wrote:

I wan to sum up values according to its font color....any idea??