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

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 <nob...@all.net> 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

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