Count formula doesn't work

G

Guest

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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Oh no, I tried the count formula on a spread sheet which contains only 6000 data and it doesnt work either
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
So are there any other formula that works like Count, count only numbers and ignore text, in excel that I can use?
 
Upvote 0
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
 
Upvote 0
Oh no, it shows FALSE. So, is there a way to change those "text" numbers to the "real" number
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top