Conditional Sum

[Abdul Shakeel]

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

 

[Mike H]

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]

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

 

[Abdul Shakeel]

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

 

[Mike H]

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]

Dear Mike

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

 

[Abdul Shakeel]

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

 

[Mike H]

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

 

[Mike H]

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