Count some of the numbers in a cell
 

Hello,
can someone please help.

I have a spreadsheet that have a "random" set of numbers in cells A:A.

Many of the numbers in A:A have numbers that starts with a fixed
number e.g 12345 the next 5 numbers is random.
Some of the others starts with 23456 the next 5 is random.

12345 and 23456 is the only leftmost numbers in A:A

Like this

1234503256
1234501204
1234599652
2345600369
2345698005


In C1 I want it to show 3 (count of numbers that starts with 12345)
In D1 I want it to show 2 (count of numbers that starts with 23456)

I have tried to combine countif and left function without solving it,
obviously. 8-) Using Office 2007

Regards
Jon

Count some of the numbers in a cell
 

Jon

Try

=COUNTIF(A1:A5,"1234500000")-COUNTIF(A1:A5,"2345600000") in C1
and
=COUNTIF(A1:A5,"2345600000") in D1.

Good luck.

Ken
Norfolk, Va



On Jul 3, 5:35 pm, Full Name wrote:
Hello,
can someone please help.

I have a spreadsheet that have a "random" set of numbers in cells A:A.

Many of the numbers in A:A have numbers that starts with a fixed
number e.g 12345 the next 5 numbers is random.
Some of the others starts with 23456 the next 5 is random.

12345 and 23456 is the only leftmost numbers in A:A

Like this

1234503256
1234501204
1234599652
2345600369
2345698005

In C1 I want it to show 3 (count of numbers that starts with 12345)
In D1 I want it to show 2 (count of numbers that starts with 23456)

I have tried to combine countif and left function without solving it,
obviously. 8-) Using Office 2007

Regards
Jon

Count some of the numbers in a cell
 

=SUMPRODUCT(--(LEFT(A1:A5,5)="12345"))
=SUMPRODUCT(--(LEFT(A1:A5,5)="23456"))
--
p45cal


"Full Name" wrote:

Hello,
can someone please help.

I have a spreadsheet that have a "random" set of numbers in cells A:A.

Many of the numbers in A:A have numbers that starts with a fixed
number e.g 12345 the next 5 numbers is random.
Some of the others starts with 23456 the next 5 is random.

12345 and 23456 is the only leftmost numbers in A:A

Like this

1234503256
1234501204
1234599652
2345600369
2345698005


In C1 I want it to show 3 (count of numbers that starts with 12345)
In D1 I want it to show 2 (count of numbers that starts with 23456)

I have tried to combine countif and left function without solving it,
obviously. 8-) Using Office 2007

Regards
Jon