Count formula doesn't work

[Guest]

Hi people
I am trying to do a counting on how many cells contain a number in a column. So I use the formula =count(A:A). I tried it on a spreadsheet which contains 10300 data in the column and it works. Then I tried it on another spread sheet which contains 50000 data in the column and it gives me a zero
I don't understand why it does that
Could someone please help me out

Thank you in advance

 

[Guest]

Oh no, I tried the count formula on a spread sheet which contains only 6000 data and it doesnt work either

 

[Paul B]

Works for me, I changed the format on some of the cells to text and it will not count them, do you have any formated as text?

 

[Guest]

Yes the format looks like this
Mill
----
55





Mill
----
55




Mill
----
55
I have a workbook contains 4 worksheets, and they all have a column with the format above
The Count formula works on one worksheet but it doesn't work on the other three worksheets

 

[Guest]

So are there any other formula that works like Count, count only numbers and ignore text, in excel that I can use?

 

[Paul B]

You can have text and numbers in the same column and count will only count the numbers, if the cell is FORMATTED as text it will still show a number but it want be counted. To check and see if excel is recognizing it as a number put this formula in a cell =ISNUMBER(A1) , if your number is in A1 it will return true if it is a "real" number and false if the cell is formatted as text

 

[Guest]

Oh no, it shows FALSE. So, is there a way to change those "text" numbers to the "real" number

 

[Paul B]

Put the number 1 in a cell, make sure it is not formatted as text, select the cell with the 1, copy, select the numbers you want to change, right click on them and paste special check multiply, ok this will change them back to "real" numbers

 

[Dave Hawley]

There are quite a few ways, but first just try formatting the column as any number format. If that fails type the number 1 in any cell (that's not formatted as Text) then copy this number, select you range of Text numbers and go to Edit>Paste special - Multiply

 

[Dave Hawley]

SNAP!