Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    count formula (2003)

    Hi,
    i have data in Column a1:a20, each cell data consist of a number and text, the text is not uniform, and i want to get their counts in a21.

    Regards,
    TIA
    dubdub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: count formula (2003)

    I am not exactly sure what you are after, but I think some of the techniques Bob Umlas does in Array Formulas should help.

    He has an array formula for:
    Extracting Just The Numeric Portion Of A Cell
    Calculating The Sum Of The Digits

    If you need additional help can you elaborate on what you want to do?
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: count formula (2003)

    Thanks Steve,

    the attached file has sample of what i want.. under the heading Soft A2:A10 i have such data, and i won get the count of the non-blank cells in a11.

    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count formula (2003)

    Try
    =counta(A2:A10) this counts all none blank cells.
    Jerry

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: count formula (2003)

    Thanks Jerry.

    dubdub
    TIA
    dubdub

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: count formula (2003)

    jerry,

    one more thing, any explanation why it does not update it self when i delete one of the values, the result still 7rather than 6.
    dubdub
    TIA
    dubdub

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count formula (2003)

    Should do

    Go to Tools|Options|Calculation

    and see if the Calculation option is set toautomatic
    Jerry

  8. #8
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: count formula (2003)

    still 7.
    TIA
    dubdub

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count formula (2003)

    It works for? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Have a look at mine and compare
    Jerry

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count formula (2003)

    Well it works perfectly well from my laptop, adding and deleting. If I change the options like I showed you in <post:=637,659>post 637,659</post:> it doesn't. This is a standard formula and nothing special about it.

    There is not much more I can say.
    Jerry

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count formula (2003)

    ahaha

    Have a look at a response from Hans <post#=632,031>post 632,031</post#> it looks like you have your calculations set to manual again Try pressing F9 and see what happens to your calculation now. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Jerry

  12. #12
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: count formula (2003)

    jerry,

    the puzzle is when i delete, when i add a new value it adjust, and guess what, when i delete the cell value using the space bar no change,still 7,and it take effect only when i use the delete button.

    dubdub
    TIA
    dubdub

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: count formula (2003)

    You have edited your message since I posted so the thread is in danger of losing sense.

    When you delete text or formulas using the space bar you are in fact just replacing the value with a space character which is an ASCII character. As it is now a value (like a letter or a number) the counta function will count it as it is no longer blank. The best thing to do is not delete using the space bar.
    Jerry

  14. #14
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: count formula (2003)

    sorry for that, and thank you for the help.
    TIA
    dubdub

Posting Permissions

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