Countif for cells starting with a number combined with text - not working

Hi, I'm having a bit of a battle trying to get this formula to work. I want to find out how many cells start with number 9

Consider the following cells in A1:A4

987-123
987-456
815-952
751-542

I'm trying COUNTIF(A:A,"9*") but it produces a result of 0.

This seems to just be a number problem. If I was to pretend I was looking for anything beginning with A then it works but just doesn't like numbers?

Hope someone can help. John

Weird, that is how it should work and how it works on mine when I test it.

When I check the cell it's saying that it's not a number (=ISNUMBER() ) so that shouldn't be the problem.

Have you checked the formatting of the cell to see what it's been seen as?

Have you checked that Automatic Calculation is switched on?

Hi, thanks for the reply. I have attached my sheet to help further! Sorry I meant to say I am using Excel 2003 - not sure if that matters

The cells you are trying to count in that workbook are numbers, not text - The examples you gave in the first post will be seen as text by Excel.

This will work for counting numbers;

Formula:

=SUMPRODUCT(--(LEFT(A:A,1)="9"))

Perfect, thank you both very much that has worked.

I did want to take it one step further and have it look at a certain cell for the "9" but that doesn't seem to work. This is a nice-to-have but if it's obvious to you then I would like it. I tried =SUMPRODUCT(--(LEFT(A:A,1)=C1)) - where C1 contains the number 9 - but it doesn't like it?

Originally Posted by johncassell

where C1 contains the number 9 - but it doesn't like it?

You can use;

Formula:

=SUMPRODUCT(--(LEFT(A:A,1)=TEXT(C1,"0")))

jeffreybrown was right, you should amend the formula to include number the row ranges as well.

Magic - thanks thats working.

=SUMPRODUCT(--(LEFT(A:A,1)=TEXT(C1,"0")))

"you should amend the formula to include number the row ranges as well" - do you mean put A1:A2000 instead of A:A ?

Originally Posted by johncassell

do you mean put A1:A2000 instead of A:A ?

Yeah, it's always better if it's a known range :-)

Yes I actually do that, thanks. Back in the early days I would put A:A for everything then wonder why the calculations were taking so long!