COUNTIF Text strings starting with numbers

[Kuih]

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]

Hi kuih:

How about ...

Book1
	A	B	C	D
1
2
3	a	6
4	b
5	c
6	12 Monkeys
7	13th Warrior
8	24 Season 1
9	24 Season 2
10	6th Day
11	8MM
12	kj
13	pqr
14
Sheet4

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

 

[Kuih]

Hi Yogi,

Thanks for your reply.

My result is = 0 titles

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

What went wrong?

 

[Scott Huish]

You can't use the whole column with SUMPRODUCT, you can only use up to 65535 rows

 

[Kuih]

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]

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]

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]

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
	A	B	C	D
1	Items
2	jklmn123		FALSE
3	a
4	b		6
5	c
6	12 Monkeys
7	13th Warrior
8	24 Season 1
9	24 Season 2
10	6th Day
11	8MM
12	kj
13	pqr
14
Sheet4

formula in cell C4 is ... =DCOUNTA(A:A,1,$C$1:$C$2)

 

[Yogi Anand]

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
	A	B	C	D
1	Items
2	jklmn123
3	a
4	b	7
5	c
6	12 Monkeys
7	13th Warrior
8	24 Season 1
9	24 Season 2
10	6th Day
11	8MM
12	kj
13	pqr
14	3bears
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]

This seems to work on whole columns...

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