count non blank cells

[Joanna_gr]

hi!

I want to use the following funtion
=IF(C5<>"";COUNTA($C$5:C5)&".";"") that counts only nonblank cells. But i also want the counting starting from the beginning after a blank cell. for example:
1. Monday
2. Tuesday

1. Friday (and not 3. as in goes normally)

Can anyone tell me how to do it?

Thanx very much

[Aladin Akyurek]

On 2002-02-25 04:54, Joanna_gr wrote:
hi!

I want to use the following funtion
=IF(C5<>"";COUNTA($C$5:C5)&".";"") that counts only nonblank cells. But i also want the counting starting from the beginning after a blank cell. for example:
1. Monday
2. Tuesday

1. Friday (and not 3. as in goes normally)

Can anyone tell me how to do it?

Thanx very much

It is not clear (to me) what you want to do. Care to provide some sample data along with the expected result(s)?

[Joanna_gr]

Using the formula
=IF(C5<>"";COUNTA($C$5:C5)&".";"")
it counts only the cells that are not empty and goes like this:

1. A
2. B
(blank cell)
3. C
4. D
(blank cell)
5. E
What i want is counting to restart after every blank cell like :
1. A
2. B
(blank cell)
1. C
2. D
(blank cell)
1. E
etc etc
I hope it's clear this time
Thanx again

[Aladin Akyurek]

On 2002-02-25 05:10, Joanna_gr wrote:
Using the formula
=IF(C5<>"";COUNTA($C$5:C5)&".";"")
it counts only the cells that are not empty and goes like this:

1. A
2. B
(blank cell)
3. C
4. D
(blank cell)
5. E
What i want is counting to restart after every blank cell like :
1. A
2. B
(blank cell)
1. C
2. D
(blank cell)
1. E
etc etc
I hope it's clear this time
Thanx again

Joanna,

In B5 enter and copy down:

=IF(LEN(C5),IF(LEN(B4),(LEFT(B4,LEN(B4)-1)+1)&".",1&"."),"")

Adapt the list separator for you seem to be using the European version of Excel.

Aladin

Addendum: The formula expects B4 to be empty!



[ This Message was edited by: Aladin Akyurek on 2002-02-25 05:35 ]

[Joanna_gr]

thank u - thank u - thank u

That works fantastic!!! How is it possible u to know everything?
thanx again

[Joanna_gr]

Another one thing please.

ok the formula =IF(LEN(C5);IF(LEN(A4);(LEFT(A4;LEN(A4)-1)+1)&".";1&".");"") works fantastic with my list, but when i need to delete a row i get the following result:
=IF(LEN(C8);IF(LEN(#REF!);(LEFT(#REF!;LEN(#REF!)-1)+1)&".";1&".");"")
and the counting stops. Is it possible the counting to continue after the row deltion?

oops! i hope that isn't too much i'm asking
thanx again

[Aladin Akyurek]

On 2002-02-25 06:04, Joanna_gr wrote:
Another one thing please.

ok the formula =IF(LEN(C5);IF(LEN(A4);(LEFT(A4;LEN(A4)-1)+1)&".";1&".");"") works fantastic with my list, but when i need to delete a row i get the following result:
=IF(LEN(C8);IF(LEN(#REF!);(LEFT(#REF!;LEN(#REF!)-1)+1)&".";1&".");"")
and the counting stops. Is it possible the counting to continue after the row deltion?

oops! i hope that isn't too much i'm asking [img]/board/images/smiles/icon_smile.gif[/img]
thanx again

Joanna,

In B5 enter and copy down:

=IF(LEN(C5),IF(LEN(OFFSET(C5,-1,-1,1,1)),(LEFT(OFFSET(C5,-1,-1,1,1),LEN(OFFSET(C5,-1,-1,1,1))-1)+1)&".",1&"."),"")

Aladin

[Joanna_gr]

Aladin,

What can I say! a "thanx" is not enough! U really made my day and my life easier. thanx thanx thanx