Count Consecutive Cells
 

I have a simple need: I have one column of data containing either an O or
a U. Is there a way I can ask EXCEL to tell me how many times the O
appeared in 2 consecutive cells, 3 consecutive cells... etc. Say I am
using collumn A for this
A
1 O
2 O
3 O
4 U
5 U
6 O
7 U
8 U
9 U
10 U

Try the following...

B1, copied down:

=SUM(--(COUNTIF(OFFSET($A$1:$A$10,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10)),0,
ROWS($B$1:B1)+1),"O")=ROWS($B$1:B1)+1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that the
result displayed in B1 will tell you how many times 'O' appeared in 2
consecutive cells, B2 will tell you how many times 'O' appeared in 3
consecutive cells, and so on.

Hope this helps!

In article ,
"Trapper via OfficeKB.com" wrote:

I have a simple need: I have one column of data containing either an O or
a U. Is there a way I can ask EXCEL to tell me how many times the O
appeared in 2 consecutive cells, 3 consecutive cells... etc. Say I am
using collumn A for this
A
1 O
2 O
3 O
4 U
5 U
6 O
7 U
8 U
9 U
10 U

Please post your question in only one newsgroup. See the responses in
Worksheet Functions

"Trapper via OfficeKB.com" wrote:

I have a simple need: I have one column of data containing either an O or
a U. Is there a way I can ask EXCEL to tell me how many times the O
appeared in 2 consecutive cells, 3 consecutive cells... etc. Say I am
using collumn A for this
A
1 O
2 O
3 O
4 U
5 U
6 O
7 U
8 U
9 U
10 U

thanks a bunch I will check it out! Sorry for the double posting.. I was
asking 2 questions in the same link and wanted to break it out!

--
Message posted via http://www.officekb.com

=COUNTIF($A$1:$A$1000,D1)-SUMPRODUCT(--($A$1:$A$999=D1),--($A$1:$A$999<$A$2
:$A$1000),--($A$2:$A$1000=$A$3:$A$1001))

where D1 holds the letter to test

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Trapper via OfficeKB.com" wrote in message
...
I have a simple need: I have one column of data containing either an O or
a U. Is there a way I can ask EXCEL to tell me how many times the O
appeared in 2 consecutive cells, 3 consecutive cells... etc. Say I am
using collumn A for this
A
1 O
2 O
3 O
4 U
5 U
6 O
7 U
8 U
9 U
10 U

ok both of these formulas work, thanks for the help! BUT I have a small
problem... the worksheet that I am trying to use these on was copied from a
table on a web page into EXCEL. The formula is not working on the column
of data that was copied from the web-page, BUT it does work when I enter my
own data. Is the format weird because of the web page it came from?
Anyone know? I tried highlighting the column and formatting as text to no
avail!

--
Message posted via http://www.officekb.com

Craig

Web Pages leave all kinds of crap in the cells.

Try David McRitchie's TRIMALL macro on your data.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Value = Application.Trim(Cell.Value)
Next Cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Mon, 23 May 2005 22:38:59 GMT, "craig trapp via OfficeKB.com"
wrote:

ok both of these formulas work, thanks for the help! BUT I have a small
problem... the worksheet that I am trying to use these on was copied from a
table on a web page into EXCEL. The formula is not working on the column
of data that was copied from the web-page, BUT it does work when I enter my
own data. Is the format weird because of the web page it came from?
Anyone know? I tried highlighting the column and formatting as text to no
avail!