Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count how many "name" in a column | Excel Worksheet Functions | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
Count number of occurences in 1 column only if something in anothe | Excel Worksheet Functions | |||
COUNT INFORMATION IN COLUMN B ONLY IF A1 MATCHES K1 COUNT THAT CE. | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |