Need to sum "last 5" red cells

[Bob Phillips]

Look at http://www.xldynamic.com/source/xld.ColourCounter.html and use
something like

=SUM(TRANSPOSE(OFFSET(A6,0,LARGE(IF(ColorIndex(A6:L6)=3,COLUMN(A6:L6)),{1,2,
3,4,5})-1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

If the data is columnar, use

=SUM(TRANSPOSE(OFFSET(A1,LARGE(IF(ColorIndex(A1:A100)=3,ROW(A1:A100)),{1,2,3
,4,5})-1,0)))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[mike]

I'm playing with some color vba from cpearson.com but can't seem to
automate the process of summing the last x number of y colored cells.
I'm having to change ranges every time I copy the formula over.

Using the info on this page, http://www.cpearson.com/excel/colors.htm,
or their own imagination, can anyone come up with a script for the
above?

My ultimate goal is to keep a running average of red cells, but I can't
do that unless I know how many I have and where to tell it to start
summing. If I can just say "sum the last 5 red cells in row A:A" then I
won't have to keep adjusting formulas.

Any comments appreciated.

 

[L. Howard Kittle]

Hi Mike,

Try this.

Sub SumColorCountRedLastFive()
Dim Red3 As Integer
Dim Count As Integer
Dim i As Integer

Range("A1000").End(xlUp).Select
i = Range("A100").End(xlUp).Row
Count = 0

For i = 1 To i - 1
If ActiveCell.Interior.ColorIndex = 3 Then
Count = Count + 1
Red3 = Red3 + ActiveCell.Value
ActiveCell.Offset(-1, 0).Select
If Count = 5 Then
Exit For
End If

ElseIf ActiveCell.Interior.ColorIndex <> 3 Then
ActiveCell.Offset(-1, 0).Select
End If
Next

MsgBox "The last five Red cells " _
& vbCrLf & "in column A add up to" _
& vbCrLf _
& vbCrLf & " " & Red3

End Sub

HTH
Regards,
Howard

 

[mike]

I can't get either to work as I need. Howard, yours works but I need to
get the last 5 in the row, not column and display in a cell as opposed
to msgbox. I can't figure out how to convert it.

Bob, I just can't get that to work. Wonder if my Excel is recognizing
the ColorIndex funtion? Excel 2000... All I get is the #NAME? result.
ColorIndex isn't in the help file anywhere.

 

[Bob Phillips]

You have to copy the ColorIndex function off of that web page into your
workbook.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

http://www.cpearson.com/excel/whatsnew.htm

on the Chip Pearson web page you can find it

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"unknown" escreveu:

 

[L. Howard Kittle]

Hi Mike,

Try this. Change the 1 in "Range("AI1").End(xlToLeft).Select" to match the
row you are using. Returns the value to cell F10.

Sub SumRedLastFiveRow()
Dim Red3 As Integer
Dim Count As Integer
Dim i As Integer

Range("AI1").End(xlToLeft).Select

i = ActiveCell.Column

Count = 0

For i = 1 To i - 1
If ActiveCell.Interior.ColorIndex = 3 Then
Count = Count + 1
Red3 = Red3 + ActiveCell.Value
ActiveCell.Offset(0, -1).Select
If Count = 5 Then
Exit For
End If

ElseIf ActiveCell.Interior.ColorIndex <> 3 Then
ActiveCell.Offset(0, -1).Select
End If
Next

'MsgBox "The last five Red cells " _
'& vbCrLf & " in row 1 add up to" _
'& vbCrLf _
'& vbCrLf & " " & Red3

Range("F10").Value = Red3
End Sub

HTH
Regards,
Howard

 

[Bob Phillips]

That one works differently, won't fit in with the formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)