COUNTIF function bug?

[Guest]

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

 

[Guest]

In a helper column enter:

=(LEFT(A1,2)="13")*1
and use countif on this column "=1"

 

[Biff]

Hi!

Try this:

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

Wildcards can only be used on TEXT.

Biff

 

[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

 

[Guest]

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