Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Count how many entries begins with a number
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Count how many entries begins with a number

    Hello,

    I have 27 rows listed # to Z. What I'm trying to do is count how many entries in my table begin with each letter as well as 0-9. I've been able to figure out the A-Z part but the number part eludes me.

    The numbers are formatted as text but also I'd like to combine them in such a way that any listing in my "Name" column that starts with a number would be counted. As an example, if "101 Dalmations" and "40 Days and 40 Nights" were listed in the name column, I'd expect to see a 2.


    This is the formula that I have working for letters.
    Code:
    =COUNTIF(tblMovies[Name],E2&"*")

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,797
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count how many entries begins with a number

    maybe: =SUMPRODUCT(--ISNUMBER(--LEFT(A1:A10,1)))
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count how many entries begins with a number

    That works perfect, thanks!

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,797
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count how many entries begins with a number

    You are welcome

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count how many entries begins with a number

    To further elaborate on that formula, is there a way to add an if statement to it? For example, I'm using the below formula to show me all the movies for "A" (which is in E2) that don't have a blank entry in the "Keep" column of the table.

    I'd like to extend that same functionality to the movies that have a name starting with a number if possible.

    Code:
    =COUNTIFS(tblMovies[Name],E2&"*",tblMovies[Keep],"<>")

  6. #6
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count how many entries begins with a number

    I think I figured it out. It does appear working but I don't know if I actually did it right. Any thoughts or advice?

    This should show all entries that are blank
    Code:
    =SUMPRODUCT(--ISNUMBER(--LEFT(tblMovies[Name],1))*(tblMovies[Keep]=""))
    This should show all entries that are not blank
    Code:
    =SUMPRODUCT(--ISNUMBER(--LEFT(tblMovies[Name],1))*(tblMovies[Keep]<>""))

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,797
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Count how many entries begins with a number

    how about PowerQuery (Get&Transform)

    Title Count Title
    101 Dalmations
    2
    101 Dalmations, 1 dvb
    40 Days and 40 Nights
    2
    40 Days and 40 Nights, 423/321
    ABC
    3
    ABC, Ala, Amee
    1 dvb
    2
    Coca-Cola, Calibration
    Coca-Cola
    1
    Phone 111
    Ala
    1
    202 jumps
    Phone 111
    1
    Bruce allmighty
    Amee
    1
    Doudi doo
    202 jumps
    1
    567 Power
    Bruce allmighty
    423/321
    Doudi doo
    567 Power
    Calibration


    or

    Criteria Count
    1
    2
    2
    1
    4
    2
    5
    1
    A
    3
    B
    1
    C
    2
    D
    1
    P
    1
    Last edited by sandy666; Jun 3rd, 2019 at 12:00 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count how many entries begins with a number

    I'll have to look into that, it sounds quite interesting. I know I can accomplish what I'm trying to do already with a pivot table, which is what I used to use but I got tired of having to refresh the data every time I made a change to my table. With writing these as formulas, they update on the fly once I make a change to the table itself.

    Its definitely worth looking into though.

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,797
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count how many entries begins with a number

    try these formulas with 1 000 000 rows and you'll see how it works

    or even 100 000
    Last edited by sandy666; Jun 3rd, 2019 at 12:17 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count how many entries begins with a number

    Yeah that makes sense. In this particular use case, its 3,133 rows, much of which are duplicates with 489 rows actually unique. I'd like to learn more about PowerQuery though, it sounds right up my alley but I don't know how useful for me since I don't really work with a large amount of data on my personal projects.

Some videos you may like

User Tag List

Tags for this Thread

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
  •