|
count distance in column
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 |
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/...soft_excel.htm Then, use the Subtotals featu http://www.officearticles.com/excel/...soft_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 "bill gras" wrote in message ... 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 |
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 "Anne Troy" wrote: 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/...soft_excel.htm Then, use the Subtotals featu http://www.officearticles.com/excel/...soft_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 "bill gras" wrote in message ... 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 |
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 bill gras wrote: 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 "Anne Troy" wrote: 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/...soft_excel.htm Then, use the Subtotals featu http://www.officearticles.com/excel/...soft_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 "bill gras" wrote in message ... 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Thank You Debra you are a gem
Bill Gras -- bill gras "Debra Dalgleish" wrote: 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 bill gras wrote: 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 "Anne Troy" wrote: 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/...soft_excel.htm Then, use the Subtotals featu http://www.officearticles.com/excel/...soft_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 "bill gras" wrote in message ... 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com