Thanks:  0
Likes:  0

# Thread: count non blank cells

1. 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

2. 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)?

3. 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

4. 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.

Addendum: The formula expects B4 to be empty!

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

5. thank u - thank u - thank u

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

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

7. On 2002-02-25 06:04, Joanna_gr wrote:

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&"."),"")

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•