Conditional Sum

M

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
 
A

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
 
A

Abdul Shakeel

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

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
 
A

Abdul Shakeel

Dear Mike

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

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
 
M

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
 
M

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
 
Top