Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
Can I sum a column based on the colors of the cells. Thua add all red, all
gree, etc. -- angelico |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
Only in XL 2007
"angelico" wrote: Can I sum a column based on the colors of the cells. Thua add all red, all gree, etc. -- angelico |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
I should have been more precise. I have XL2007. My question is how? Thanks
-- angelico "Lerner" wrote: Only in XL 2007 "angelico" wrote: Can I sum a column based on the colors of the cells. Thua add all red, all gree, etc. -- angelico |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
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" wrote: I should have been more precise. I have XL2007. My question is how? Thanks -- angelico "Lerner" wrote: Only in XL 2007 "angelico" wrote: Can I sum a column based on the colors of the cells. Thua add all red, all gree, etc. -- angelico |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
Feedback if not clear or if it is!
Tx "Lerner" wrote: 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" wrote: I should have been more precise. I have XL2007. My question is how? Thanks -- angelico "Lerner" wrote: Only in XL 2007 "angelico" wrote: Can I sum a column based on the colors of the cells. Thua add all red, all gree, etc. -- angelico |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
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 -- angelico "Lerner" wrote: Feedback if not clear or if it is! Tx "Lerner" wrote: 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" wrote: I should have been more precise. I have XL2007. My question is how? Thanks -- angelico "Lerner" wrote: Only in XL 2007 "angelico" wrote: Can I sum a column based on the colors of the cells. Thua add all red, all gree, etc. -- angelico |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
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. "angelico" wrote: 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 -- angelico "Lerner" wrote: Feedback if not clear or if it is! Tx "Lerner" wrote: 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" wrote: I should have been more precise. I have XL2007. My question is how? Thanks -- angelico "Lerner" wrote: Only in XL 2007 "angelico" wrote: Can I sum a column based on the colors of the cells. Thua add all red, all gree, etc. -- angelico |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum
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 "angelico" wrote: Can I sum a column based on the colors of the cells. Thua add all red, all gree, etc. -- angelico |
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 |