count non blank cells

Joanna_gr

Board Regular
Joined
Feb 16, 2002
Messages
149
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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)?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
thank u - thank u - thank u

That works fantastic!!! How is it possible u to know everything? :)
thanx again :)
 
Upvote 0
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
 
Upvote 0
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 /board/images/smiles/icon_smile.gif
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
 
Upvote 0
Aladin,

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

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top