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

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

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