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

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

Hi!

Try this:

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

Wildcards can only be used on TEXT.

Biff

"raigo" wrote in message
...
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

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" wrote in message
...
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

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

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

Don't use the wildcard!

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

Biff

"raigo" wrote in message
...
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