# COUNTIF Text strings starting with numbers

#### Kuih

##### New Member
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Yogi Anand

##### MrExcel MVP
Hi kuih:

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))

#### Kuih

##### New Member
Hi Yogi,

My result is = 0 titles

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

What went wrong?

#### Scott Huish

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

#### Kuih

##### New Member
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
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
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
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)

#### Yogi Anand

##### MrExcel MVP
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))

#### Drew

##### Board Regular
This seems to work on whole columns...

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

Replies
3
Views
417
Replies
3
Views
581
Replies
6
Views
3K
Replies
3
Views
1K
Replies
3
Views
360

1,195,667
Messages
6,011,049
Members
441,580
Latest member
BornholmerBjarne

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

### Which adblocker are you using?

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

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