Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count help with numbering Rows?
In one row I have information. What I want to do is if there is information
in that row I want excel to count. Say I have a range with 20 rows. In the first five rows there is no information but in the 6th row there is information. I want to assign the number 1 (This can be in a seperate column.) Then say there are 2 or 3 blank rows and another set of information comes up in that seperate column I want the number two to show up and so on. Here is my original formula but all I ever get is 1 and 2 even if multiply entrees. =count(offset(A1,0,0,Row(1:1) This formula is in a seperate column. Going down the column this next formula would be =count(offset(A2,0,0,Row(2:2) and so on If this formula goes down 20 rows I get what I want on the first 2 (Numbered 1 and 2) but by the third entree 4 of 5 rows down I get 1 again. Thanks Scott |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count help with numbering Rows?
=COUNTA()
- will count all non blank rows where the brackets define the cell range that you wish to cover. Please hit Yes if my comments have helped. Thanks. "scott" wrote: In one row I have information. What I want to do is if there is information in that row I want excel to count. Say I have a range with 20 rows. In the first five rows there is no information but in the 6th row there is information. I want to assign the number 1 (This can be in a seperate column.) Then say there are 2 or 3 blank rows and another set of information comes up in that seperate column I want the number two to show up and so on. Here is my original formula but all I ever get is 1 and 2 even if multiply entrees. =count(offset(A1,0,0,Row(1:1) This formula is in a seperate column. Going down the column this next formula would be =count(offset(A2,0,0,Row(2:2) and so on If this formula goes down 20 rows I get what I want on the first 2 (Numbered 1 and 2) but by the third entree 4 of 5 rows down I get 1 again. Thanks Scott |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count help with numbering Rows?
Scott -
This way will work, but it will take two columns. I will describe this using columns B and C, with your data in column A. You can hide column B, or you can copy/paste special column C when you are done and then delete column B. Here goes: In B1, enter =IF(len(TRIM(A1))0,1,0) In B2, enter =IF(len(TRIM(A2))0,B1+1,B1) drag or copy/paste the formula in B2 down the column In C1, enter =IF(B1=0,"",B1) In C2, enter =IF(B2=B1,"",B2) drag or copy/paste the formula in C2 down the column At this point, C will contain what you want. -- Daryl S "scott" wrote: In one row I have information. What I want to do is if there is information in that row I want excel to count. Say I have a range with 20 rows. In the first five rows there is no information but in the 6th row there is information. I want to assign the number 1 (This can be in a seperate column.) Then say there are 2 or 3 blank rows and another set of information comes up in that seperate column I want the number two to show up and so on. Here is my original formula but all I ever get is 1 and 2 even if multiply entrees. =count(offset(A1,0,0,Row(1:1) This formula is in a seperate column. Going down the column this next formula would be =count(offset(A2,0,0,Row(2:2) and so on If this formula goes down 20 rows I get what I want on the first 2 (Numbered 1 and 2) but by the third entree 4 of 5 rows down I get 1 again. Thanks Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Numbering - do not want to count first row | Setting up and Configuration of Excel | |||
Numbering Rows | Excel Worksheet Functions | |||
Numbering Rows | Excel Worksheet Functions | |||
Numbering Rows | Excel Discussion (Misc queries) | |||
How do I do numbering of rows in Excel when there are blank rows? | Excel Discussion (Misc queries) |