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

1. ## 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. ## Re: Count how many entries begins with a number

maybe: =SUMPRODUCT(--ISNUMBER(--LEFT(A1:A10,1)))

3. ## Re: Count how many entries begins with a number

That works perfect, thanks!

4. ## Re: Count how many entries begins with a number

You are welcome

Have a nice day

5. ## 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. ## 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. ## Re: Count how many entries begins with a number

 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

8. ## 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. ## 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

10. ## 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.