| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
I wan to sum up values according to its font color....any idea??
|
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
| Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
