In article ,
Excel Curious wrote:

I'm trying write a formula to count the duplicates in a column where they
appear contiguously (adjacent to one another). Another way of putting this is
to count the number of times a number appears continuously in a column before
it changes to another number. I have Excel 2007.

Example:
ColumnA ColumnB ColumnC(answer)
0001 30 2
0001 30
0001 70 1
0001 30 2
0001 30

Your help is greatly appreciated


Let's assume that Column B contains the data, starting at Row 2. Then
try...

C1:

=MATCH(9.99999999999999E+307,B:B)

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(B2<B1,COUNTIF(B2:INDEX(B2:INDEX(B:B,$C$1),LOO KUP(9.99999999999999E+3
07,CHOOSE({1,2},$C$1-ROW(B2)+1,MATCH(TRUE,B2:INDEX(B:B,$C$1)<B2,0)))), B2
),"")

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions