Excel Summif by colour or style

[nberryman]

Can I use a Sumif calculation based on the style or colour of a cell?

The cells have values in them and are colour coded to designate the person the values refer to. Is it possible to add up the valuse using the colour as the criteria.

Thanks in advance guys



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[xlbo]

Nope - not without creating your own function
I have said this before recently - it is VERY bad spreadsheet design to use colours to create conditions - colours are very nice for visual presentations but an absolute nightmare for referencing data. You should always try and use hard data to reference conditions

One thing you could do is download asap-utilities addin which has a function that can do a sum based on FONT colour
(

http://www.asap-utilities.com)

Other than that, you would need something like:

Function sumcol(rng As Range, clr As String)
Dim ctrCol As Integer
Dim tempsum As Long
tempsum = 0
Select Case UCase(clr)
Case "RED"
ctrCol = 3
Case "YELLOW"
ctrCol = 6
Case "BLUE"
ctrCol = 5
Case "GREEN"
ctrCol = 4
Case "PURPLE"
ctrCol = 13
Case "PINK"
ctrCol = 7
Case "GREY"
ctrCol = 15
Case Else
sumcol = "Invalid Colour selected"
End Select

For Each c In rng
If c.Interior.ColorIndex = ctrCol Then
tempsum = tempsum + c.Value
Else
End If
Next
sumcol = tempsum
End Function

Please note that this will only work for those colours that I have entered - if you need to be able to use more colours, run this sub:

Sub listcols()
For i = 1 To 56
Range("A" & i).Interior.ColorIndex = i
Next i
End Sub

this will show all colours and the ROW number that they are on will refer to the would be what you assign to ctrcol in the function

To use the function, you would enter something like

=sumcol(A1:A100,"red&quot
which will sum all values in red in A1:A100

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[nberryman]

I agree with the design comments using colours but I didn't create the sheet so I'm stuck with it.

Thanks for the help

Neil

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com