I have an excel where in column A i want to create an auto numbering.
In column B i have some codes and value No.
SO i want to count only the lines that have codes, an no the lines that have
value No.

A B
1 sub1
2 sub2
NO
3 sub3

thanks in advance for your prompt response

Manos

Manos wrote:
I have an excel where in column A i want to create an auto numbering.
In column B i have some codes and value No.
SO i want to count only the lines that have codes, an no the lines that have
value No.

A B
1 sub1
2 sub2
NO
3 sub3

thanks in advance for your prompt response

Manos

Assuming your data starts in row 1, put the following in A1:

=IF(B1="NO","",COUNTIF($B$1:B1,"<NO"))

Glenn wrote:
Manos wrote:
I have an excel where in column A i want to create an auto numbering.
In column B i have some codes and value No.
SO i want to count only the lines that have codes, an no the lines
that have value No.

A B 1 sub1
2 sub2
NO
3 sub3

thanks in advance for your prompt response

Manos

Assuming your data starts in row 1, put the following in A1:

=IF(B1="NO","",COUNTIF($B$1:B1,"<NO"))

And then fill down as needed.

Hi,

Put a 1 in a1 then this formula in a2 and drag down

=IF(B2<"No",MAX($A$1:A1)+1,"")

Mike

"Manos" wrote:

I have an excel where in column A i want to create an auto numbering.
In column B i have some codes and value No.
SO i want to count only the lines that have codes, an no the lines that have
value No.

A B
1 sub1
2 sub2
NO
3 sub3

thanks in advance for your prompt response

Manos

Assuming that you want the numbering to skip if column contaibns "No", try
entering this into A1 and copying it down:-

=IF(B1<"No",ROW(B1)-COUNTIF($B$1:B1,"No"),"")
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Manos" wrote:

I have an excel where in column A i want to create an auto numbering.
In column B i have some codes and value No.
SO i want to count only the lines that have codes, an no the lines that have
value No.

A B
1 sub1
2 sub2
NO
3 sub3

thanks in advance for your prompt response

Manos

Assuming you start number in A1...
A1: =IF($B1="No","",ROW()-COUNTIF($B$1:$B1,"No"))
If you are starting your numbering anywhere below that, just subtract an
additional number that is 1 less than the current row, and change your range
accordingly...
i.e.
A4: =IF($B4="No","",ROW()-COUNTIF($B$4:$B4,"No"))
--
** John C **


"Manos" wrote:

I have an excel where in column A i want to create an auto numbering.
In column B i have some codes and value No.
SO i want to count only the lines that have codes, an no the lines that have
value No.

A B
1 sub1
2 sub2
NO
3 sub3

thanks in advance for your prompt response

Manos

Hi,

Really not much of an improvement, but a tiny bit shorter:

=IF(B2="No","",MAX(A$1:A1)+1)

--
Thanks,
Shane Devenshire


"Manos" wrote:

I have an excel where in column A i want to create an auto numbering.
In column B i have some codes and value No.
SO i want to count only the lines that have codes, an no the lines that have
value No.

A B
1 sub1
2 sub2
NO
3 sub3

thanks in advance for your prompt response

Manos