+ Reply to Thread
Results 1 to 13 of 13

Count word/number without criteria

  1. 09-06-2012, 04:39 AM #1
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Count word/number without criteria

    Good day!

    Can you guys help me on this one?

    I want to count the number of words/number on a column without
    having specific criteria? I can get result if it is on database
    (i.e "Select distinct(weeknum") from table1") etc.

    How can i have this output below using function in excel?

    count.JPG


    Thanks,

    Gerald
    Last edited by thisisgerald; 09-06-2012 at 04:43 AM.
    Reply With QuoteRegister To Reply
  2. 09-06-2012, 04:44 AM #2
    Macdave_19
    Macdave_19 is offline
    Valued Forum Contributor Macdave_19's Avatar
    Join Date
    03-14-2007
    Location
    Birmingham, England
    MS-Off Ver
    12.0
    Posts
    808

    Re: Count word/number without criteria

    THis uses a scripting Dictionary and late binding s you dont need a reference:

    Please Login or Register  to view this content.
    Sorry should have explained how to use this:

    In your workbook press ALT+F11
    A window Should appear, in that window select "Insert>Module"
    On the left hand pane a module should appear.
    Double click on that module and paste the above into it
    Now you can close that window and use it like any other function by typing "=Distinct..."

    Cheers
    Last edited by Macdave_19; 09-06-2012 at 04:59 AM. Reason: further info
    Mr MaGoo
    Magoo.Inc MMVII

    If i've helped please add to my Rep by Clicking on the Blue Scales in the top right hand corner of the post
    Reply With QuoteRegister To Reply
  3. 09-06-2012, 04:46 AM #3
    Macdave_19
    Macdave_19 is offline
    Valued Forum Contributor Macdave_19's Avatar
    Join Date
    03-14-2007
    Location
    Birmingham, England
    MS-Off Ver
    12.0
    Posts
    808

    Re: Count word/number without criteria

    Also if you need to use this on multiple range i can edit it to allow ParamArrays if you like?
    Reply With QuoteRegister To Reply
  4. 09-06-2012, 05:06 AM #4
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Count word/number without criteria

    Thanks for the help Macdave_19!

    I have tried your scripting and i got a record of 4 (which are the count of distinct records)

    How to display those distinct result?

    ex.

    Weeknum #ofRecord
    23 6
    24 3
    25 5
    26 2

    Thanks in advance!
    Reply With QuoteRegister To Reply
  5. 09-06-2012, 05:07 AM #5
    duanzhuanming
    duanzhuanming is offline
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: Count word/number without criteria

    Bonjour,
    you can use function "countif" for counting Word/number
    See attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-06-2012, 05:11 AM #6
    Macdave_19
    Macdave_19 is offline
    Valued Forum Contributor Macdave_19's Avatar
    Join Date
    03-14-2007
    Location
    Birmingham, England
    MS-Off Ver
    12.0
    Posts
    808

    Re: Count word/number without criteria

    Sorry didn't look at the image! yeah sue countif its very easy
    Reply With QuoteRegister To Reply
  7. 09-06-2012, 05:15 AM #7
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Count word/number without criteria

    Quote Originally Posted by duanzhuanming View Post
    Bonjour,
    you can use function "countif" for counting Word/number
    See attached file.
    Thanks for this one! But i also need to display those records in unique form.

    Kindly check the image. Thanks
    Reply With QuoteRegister To Reply
  8. 09-06-2012, 05:19 AM #8
    duanzhuanming
    duanzhuanming is offline
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: Count word/number without criteria

    Do you want to count how many words or numbers in a column?
    =Sumproduct(1/Countif(A1:A100,A1:A100))
    A1:A100: range that you want to count words or numbers in..
    Reply With QuoteRegister To Reply
  9. 09-06-2012, 05:22 AM #9
    duanzhuanming
    duanzhuanming is offline
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: Count word/number without criteria

    Quote Originally Posted by thisisgerald View Post
    Thanks for this one! But i also need to display those records in unique form.

    Kindly check the image. Thanks
    You can use "Advance filter". it's very easy..
    Reply With QuoteRegister To Reply
  10. 09-06-2012, 05:25 AM #10
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Count word/number without criteria

    Quote Originally Posted by duanzhuanming View Post
    Do you want to count how many words or numbers in a column?
    =Sumproduct(1/Countif(A1:A100,A1:A100))
    A1:A100: range that you want to count words or numbers in..
    Yes. I'm sorry! I'm hungry just a while ago That's what i'm looking for.

    Follow up question, how can i display those unique records from the Column A?

    Thanks,
    thisisgerald
    Reply With QuoteRegister To Reply
  11. 09-06-2012, 05:26 AM #11
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Count word/number without criteria

    Quote Originally Posted by duanzhuanming View Post
    You can use "Advance filter". it's very easy..
    Sorry again, i haven't mentioned that i'll be using macro to get those records. :D
    Reply With QuoteRegister To Reply
  12. 09-06-2012, 06:03 AM #12
    duanzhuanming
    duanzhuanming is offline
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: Count word/number without criteria

    Quote Originally Posted by thisisgerald View Post
    Sorry again, i haven't mentioned that i'll be using macro to get those records. :D
    There's many ways to do it.
    with macro:
    Sub Distinct()
    Sheets("Data").Range("A1:A65536").AdvancedFilter xlFilterCopy, , [D1], True
    End Sub

    -Range("A1:A65536"): Your data
    -[D1]: Copy to range
    with function
    see attached file
    Bien cordialement,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 09-06-2012, 06:22 AM #13
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Count word/number without criteria

    Quote Originally Posted by duanzhuanming View Post
    There's many ways to do it.
    with macro:
    Sub Distinct()
    Sheets("Data").Range("A1:A65536").AdvancedFilter xlFilterCopy, , [D1], True
    End Sub

    -Range("A1:A65536"): Your data
    -[D1]: Copy to range
    with function
    see attached file
    Bien cordialement,

    Thanks duanzhuanming! I also found it. :D Thanks for the help!

    Until next time.

    Thanks and regards,
    Gerald
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1