count distance in column

[Guest]

I have in cells B1 to B90 the word " distance" in different rows at random ,
other rows have numbers in them. What I need is the word "distance" to have a
number before it , in column A . Every time the word "distance" comes up it
needs to be added to the last "distance" word, like : distance=1,
distance+distance=2, distance+distance+distance=3 and so on.
eg:- A1 1 B1 distance
A2 (blank) B2 22.7
A3 (blank) B3 33.8
A4 2 B4 distance
-- A5 (blank) B5 23
A6 (blank) B6 5.8
A7 (blank) B7 6
A8 (blank) B8 17
A9 3 B9 distance
A10 (blank) B10 34.9
Down to 300 rows

Hope some can help
thanks
bill gras

 

[Anne Troy]

Hi, Bill. Instead of using VBA or something here, why don't you do the
following? First, fill int he blank cells using these instructions:

http://www.officearticles.com/excel/fill_in_the_blanks_in_microsoft_excel.htm

Then, use the Subtotals feature:

http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm

By doing it this way, you can get subtotals (and clear them again) any time
you want. The only differance is that instead of Distance, it'll say
"Total".
*******************
~Anne Troy

www.OfficeArticles.com

 

[Guest]

Hi Anne Troy

Thank you for your reply
I can't change the word "distance" because that is the result of a worksheet
formula
I would be much happier with a worksheet function if there is one

regards
bill gras

 

[Debra Dalgleish]

Type the number 1 in cell A1.
In cell A2, enter the formula:
=IF(B2="distance",MAX($A$1:A1)+1,"")

Copy the formula down to row 300

 

[Guest]

Thank You Debra you are a gem
Bill Gras