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!
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi kuih:

How about ...
Book1
ABCD
1
2
3a
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))
 

Kuih

New Member
Joined
May 24, 2005
Messages
29
Hi Yogi,

Thanks for your reply.

My result is = 0 titles

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

What went wrong?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
You can't use the whole column with SUMPRODUCT, you can only use up to 65535 rows
 

Kuih

New Member
Joined
May 24, 2005
Messages
29
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.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
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))
 

Kuih

New Member
Joined
May 24, 2005
Messages
29
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!
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
2jklmn123
3a
4b
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)
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
4b
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))
 

Drew

Board Regular
Joined
Feb 18, 2002
Messages
182
This seems to work on whole columns...

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

Forum statistics

Threads
1,077,782
Messages
5,336,284
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top