Counting the number of consecutive cells/rows that have the same entry
 

Hi,
I have tried using the count functions in excell to count the number
of cells that are in consecutive order that have the same value, but I
can not get my head around it. I have rows of data with fixed entries
say A B C D and E and i want to put into the column next to the
entries a formula /vba programme that counts the the numbers above and
below if they = the number in the cell and only until there is a
change in numbers e.g

Column A Column B
A 2
A 2
B 1
C 2
C 2
B 1
D 2
D
C
C
C
D
E

Counting the number of consecutive cells/rows that have the same entry
 

Counting the number of consecutive cells/rows that have the same entry
 

On 2 Nov 2004 18:05:13 -0800, (Chantelle)
wrote:

Hi,
I have tried using the count functions in excell to count the number
of cells that are in consecutive order that have the same value, but I
can not get my head around it. I have rows of data with fixed entries
say A B C D and E and i want to put into the column next to the
entries a formula /vba programme that counts the the numbers above and
below if they = the number in the cell and only until there is a
change in numbers e.g

Column A Column B
A 2
A 2
B 1
C 2
C 2
B 1
D 2
D
C
C
C
D
E

Take a look at the Data/Subtotal wizard. Perhaps it will easily do what you
want.



--ron

Counting the number of consecutive cells/rows that have the same entry
 

Hi Chantelle,

I have tried using the count functions in excell to count the number
of cells that are in consecutive order that have the same value, but I
can not get my head around it. I have rows of data with fixed entries
say A B C D and E and i want to put into the column next to the
entries a formula /vba programme that counts the the numbers above and
below if they = the number in the cell and only until there is a
change in numbers e.g

You can achieve this using worksheet functions, but it takes two columns:

A B C
1 Col1 Col2 Col3
2 A =IF(A2=A1,B1+1,1) =IF(A2=A3,C3,B2)
3 A ...copy down... ...copy down...
4 B

Column C then contains the numbers you want.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk