Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: count non blank cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thank u - thank u - thank u

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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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