COUNTIF function bug?

[raigo]

Hello, i am having trouble with the countif function. I need to count the
cells which begin with a certain numeric phrase. For example, i want excel to
count cells that start with "13". Then i would enter the following code:
=COUNTIF(A1:A10;"13*"), but it doesn`t work. It works fine with words but
gets in trouble when cells contain numbers. Is it a bug or is there something
wrong with the formula?

Example:
213
1345
13,34
13.44
1413

It should count cells 2, 3 and 4 because they start with 13. The answer
should be 3...

Thank You,
Raigo Tingas

[Gary''s Student]

In a helper column enter:

=(LEFT(A1,2)="13")*1
and use countif on this column "=1"
--
Gary's Student


"raigo" wrote:

> Hello, i am having trouble with the countif function. I need to count the
> cells which begin with a certain numeric phrase. For example, i want excel to
> count cells that start with "13". Then i would enter the following code:
> =COUNTIF(A1:A10;"13*"), but it doesn`t work. It works fine with words but
> gets in trouble when cells contain numbers. Is it a bug or is there something
> wrong with the formula?
>
> Example:
> 213
> 1345
> 13,34
> 13.44
> 1413
>
> It should count cells 2, 3 and 4 because they start with 13. The answer
> should be 3...
>
> Thank You,
> Raigo Tingas

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(LEFT(A1:A6,2)="13"))

Wildcards can only be used on TEXT.

Biff

"raigo" <raigo@discussions.microsoft.com> wrote in message
news:6E6454FD-A281-4ABA-AE7B-701170B2756E@microsoft.com...
> Hello, i am having trouble with the countif function. I need to count the
> cells which begin with a certain numeric phrase. For example, i want excel
> to
> count cells that start with "13". Then i would enter the following code:
> =COUNTIF(A1:A10;"13*"), but it doesn`t work. It works fine with words but
> gets in trouble when cells contain numbers. Is it a bug or is there
> something
> wrong with the formula?
>
> Example:
> 213
> 1345
> 13,34
> 13.44
> 1413
>
> It should count cells 2, 3 and 4 because they start with 13. The answer
> should be 3...
>
> Thank You,
> Raigo Tingas

[Bernard Liengme]

Since numbers are stored in binary, 13.25 will not get counted in your
COUNTIF formula.
Gary has one answer, here's another
EITHER format the range as text before entering the values and use COUNTIF
OR in helper column use =TEXT(a1,"0"), copy down column and use COUNTIF on
theses values
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:D0DD87A9-CCA1-4A0A-96B4-3C5EE458748F@microsoft.com...
> In a helper column enter:
>
> =(LEFT(A1,2)="13")*1
> and use countif on this column "=1"
> --
> Gary's Student
>
>
> "raigo" wrote:
>
>> Hello, i am having trouble with the countif function. I need to count the
>> cells which begin with a certain numeric phrase. For example, i want
>> excel to
>> count cells that start with "13". Then i would enter the following code:
>> =COUNTIF(A1:A10;"13*"), but it doesn`t work. It works fine with words but
>> gets in trouble when cells contain numbers. Is it a bug or is there
>> something
>> wrong with the formula?
>>
>> Example:
>> 213
>> 1345
>> 13,34
>> 13.44
>> 1413
>>
>> It should count cells 2, 3 and 4 because they start with 13. The answer
>> should be 3...
>>
>> Thank You,
>> Raigo Tingas

[raigo]

Thank you all for your replies, unfortunately i still cant get it to work
It gives me a zero on all of these 3 options. For example:
=SUMPRODUCT(--(LEFT(H2:H1062;2)="17*")) gives a zero. I had to replace ","
with ";" as the formula was incorrect if there was ","
Any more ideas?

Thank You

[Biff]

>=SUMPRODUCT(--(LEFT(H2:H1062;2)="17*"))

Don't use the wildcard!

=SUMPRODUCT(--(LEFT(H2:H1062;2)="17"))

Biff

"raigo" <raigo@discussions.microsoft.com> wrote in message
news:CBC8E83D-4AF8-4A46-93EA-4E22986E8321@microsoft.com...
> Thank you all for your replies, unfortunately i still cant get it to work
>
> It gives me a zero on all of these 3 options. For example:
> =SUMPRODUCT(--(LEFT(H2:H1062;2)="17*")) gives a zero. I had to replace ","
> with ";" as the formula was incorrect if there was ","
> Any more ideas?
>
> Thank You