Conditional Sum

[angelico]

Can I sum a column based on the colors of the cells. Thua add all red, all
gree, etc.

 

[Lerner]

Only in XL 2007

 

[angelico]

I should have been more precise. I have XL2007. My question is how? Thanks

 

[Lerner]

First, I'm not one of the experts in this group, as a matter of fact I'm new
at XL, but I improvise a lot, and as XL does not do
interpretation of colors, this is what I do:
In XL 2007 I can filter by color.(after auto filtering the data)
Then in any cell I apply this formula to SUM the amount in column F
=subtotal(9,F2:F4545)
change the ranges as needed.
NOW, if you need to count items then wait for the answer I posted previous to
your question( if that can be done).
We, however, need some light from the experts regarding these statements.
THANKS.

 

[Lerner]

Feedback if not clear or if it is!
Tx

First, I'm not one of the experts in this group, as a matter of fact I'm new
at XL, but I improvise a lot, and as XL does not do
interpretation of colors, this is what I do:
In XL 2007 I can filter by color.(after auto filtering the data)
Then in any cell I apply this formula to SUM the amount in column F
=subtotal(9,F2:F4545)
change the ranges as needed.
NOW, if you need to count items then wait for the answer I posted previous to
your question( if that can be done).
We, however, need some light from the experts regarding these statements.
THANKS.

 

[angelico]

Very clear. Excellent. A shame that given the ability to filter by a color
they didn't allow a cell with a color to be used as an argument in "sumif"
Thanks

 

[Lerner]

You're very welcome, glad I could help.
I think there is another way using macro and/or functions,
but the answer will come from the nice people that help us daily.

 

[francis]

Hi Angel

I am not sure of 2007 version as I do not own one.
You can find the solution you are seeking here at Chip Pearson's great site,
Look at tha section on Operations With Color Index Values

http://www.cpearson.com/excel/colors.aspx

The SumColor function's code is reproduced here

Function SumColor(TestRange As Range, SumRange As Range, _
ColorIndex As Long, Optional OfText As Boolean = False) As Variant
Dim D As Double
Dim N As Long
Dim CI As Long

Application.Volatile True
If (TestRange.Areas.Count > 1) Or _
(SumRange.Areas.Count > 1) Or _
(TestRange.Rows.Count <> SumRange.Rows.Count) Or _
(TestRange.Columns.Count <> SumRange.Columns.Count) Then
SumColor = CVErr(xlErrRef)
Exit Function
End If

If ColorIndex = 0 Then
If OfText = False Then
CI = xlColorIndexNone
Else
CI = xlColorIndexAutomatic
End If
Else
CI = ColorIndex
End If

Select Case CI
Case 0, xlColorIndexAutomatic, xlColorIndexNone
' ok
Case Else
If IsValidColorIndex(ColorIndex:=ColorIndex) = False Then
SumColor = CVErr(xlErrValue)
Exit Function
End If
End Select

For N = 1 To TestRange.Cells.Count
With TestRange.Cells(N)
If OfText = True Then
If .Font.ColorIndex = CI Then
If IsNumeric(.Value) = True Then
D = D + .Value
End If
End If
Else
If .Interior.ColorIndex = CI Then
If IsNumeric(.Value) = True Then
D = D + .Value
End If
End If
End If
End With
Next N

SumColor = D

End Function


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis