COUNTIF Text strings starting with numbers

Kuih

New Member
Joined
May 24, 2005
Messages
29
Dear Excel Experts,

I'm compiling my own DVD list. I'm using the following formula to calculate number of titles starting with letter A:

=COUNTIF(A:A,"A*")

However, how do I calculate titles that starts with numbers?

For example:

12 Monkeys
13th Warrior
24 Season 1
24 Season 2
6th Day
8MM

= Result 6 titles

Please help. Many thanks in advance. Cheers!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi kuih:

How about ...
Book1
ABCD
1
2
3a6
4b
5c
612 Monkeys
713th Warrior
824 Season 1
924 Season 2
106th Day
118MM
12kj
13pqr
14
Sheet4


formula in cell B3 is ... =SUMPRODUCT(--ISNUMBER(LEFT(A3:A13)+0))
 
Upvote 0
Hi Yogi,

Thanks for your reply.

My result is = 0 titles

=SUMPRODUCT(--ISNUMBER(LEFT(A:A)+0))

What went wrong?
 
Upvote 0
You can't use the whole column with SUMPRODUCT, you can only use up to 65535 rows
 
Upvote 0
Oh, ok thanks. I could only wish that there's a better solution that captures the entire column, this way I don't need to make changes to the range everytime list grows longer.
 
Upvote 0
It's a little slow to calculate this for the whole column (-1 row), but if you put a range that wasn't so high but perhaps way beyond what would ever happen:

Perhaps, if you would never have more than 10000 DVDs:

=SUMPRODUCT(--ISNUMBER(LEFT(A1:A10000)+0))
 
Upvote 0
HOTPEPPER said:
It's a little slow to calculate this for the whole column (-1 row), but if you put a range that wasn't so high but perhaps way beyond what would ever happen:

Perhaps, if you would never have more than 10000 DVDs:

=SUMPRODUCT(--ISNUMBER(LEFT(A1:A10000)+0))

Haha, you're right. I may not be having 10,000 DVD titles starting with numbers in near future but the application of this formula is not restricted only to my purpose.

Thanks for your kind replies! Have a nice day!
 
Upvote 0
Kuih said:
Oh, ok thanks. I could only wish that there's a better solution that captures the entire column, this way I don't need to make changes to the range everytime list grows longer.
Hi Kuih:

If you must use whole column, we can use the DCOUNTA function as in the following ...
y051121h1.xls
ABCD
1Items
2jklmn123FALSE
3a
4b6
5c
612 Monkeys
713th Warrior
824 Season 1
924 Season 2
106th Day
118MM
12kj
13pqr
14
Sheet4


formula in cell C4 is ... =DCOUNTA(A:A,1,$C$1:$C$2)
 
Upvote 0
However, even with using the SUMPRODUCT function you can use a dynamic range that can automatically grow as your collection grows.

In the following illustration ...
y051121h1.xls
ABCD
1Items
2jklmn123
3a
4b7
5c
612 Monkeys
713th Warrior
824 Season 1
924 Season 2
106th Day
118MM
12kj
13pqr
143bears
15
Sheet4 (2)


formula in cell B4 is ... =SUMPRODUCT(--ISNUMBER(LEFT(yRange)+0))

yRange has been defined as a dynamic range ...

=$A$1:INDEX($A:$A,COUNTA($A:$A))
 
Upvote 0
This seems to work on whole columns...

=SUM(COUNTIF(A:A,{0,1,2,3,4,5,6,7,8,9}&"*"))
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top