Count how many entries begins with a number

[reaktorblue]

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.

=COUNTIF(tblMovies[Name],E2&"*")

 

[sandy666]

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

 

[reaktorblue]

That works perfect, thanks!

 

[sandy666]

You are welcome

Have a nice day

 

[reaktorblue]

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.

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

 

[reaktorblue]

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

=SUMPRODUCT(--ISNUMBER(--LEFT(tblMovies[Name],1))*(tblMovies[Keep]=""))

This should show all entries that are not blank

=SUMPRODUCT(--ISNUMBER(--LEFT(tblMovies[Name],1))*(tblMovies[Keep]<>""))

 

[sandy666]

how about PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Title[/td][td][/td][td=bgcolor:#70AD47]Count[/td][td=bgcolor:#70AD47]Title[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]101 Dalmations[/td][td][/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]101 Dalmations, 1 dvb[/td][/tr]

[tr=bgcolor:#FFFFFF][td]40 Days and 40 Nights[/td][td][/td][td]

2​

[/td][td]40 Days and 40 Nights, 423/321[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]ABC, Ala, Amee[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1 dvb[/td][td][/td][td]

2​

[/td][td]Coca-Cola, Calibration[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Coca-Cola[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Phone 111[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ala[/td][td][/td][td]

1​

[/td][td]202 jumps[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Phone 111[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Bruce allmighty[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Amee[/td][td][/td][td]

1​

[/td][td]Doudi doo[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]202 jumps[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]567 Power[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Bruce allmighty[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]423/321[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Doudi doo[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]567 Power[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Calibration[/td][td][/td][td][/td][td][/td][/tr]
[/table]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Criteria[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]4[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]5[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]P[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]
[/table]

 

[reaktorblue]

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.

 

[sandy666]

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

or even 100 000

 

[reaktorblue]

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.