Count formula doesn't work

Mar 5th, 2002, 07:41 PM

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

Mar 5th, 2002, 07:45 PM

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

Paul B · Mar 5th, 2002, 07:59 PM

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?

Mar 5th, 2002, 08:08 PM

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

Mar 5th, 2002, 08:12 PM

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

Paul B · Mar 5th, 2002, 08:25 PM

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

Mar 5th, 2002, 08:28 PM

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

Paul B · Mar 5th, 2002, 08:40 PM

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 · Mar 5th, 2002, 08:41 PM

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 · Mar 5th, 2002, 08:41 PM

SNAP!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 07:41 PM	#1
Guest Posts: n/a	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

Mar 5th, 2002, 07:45 PM	#2
Guest Posts: n/a	Oh no, I tried the count formula on a spread sheet which contains only 6000 data and it doesnt work either

Mar 5th, 2002, 07:59 PM	#3
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	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?

Mar 5th, 2002, 08:08 PM	#4
Guest Posts: n/a	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

Mar 5th, 2002, 08:12 PM	#5
Guest Posts: n/a	So are there any other formula that works like Count, count only numbers and ignore text, in excel that I can use?

Mar 5th, 2002, 08:25 PM	#6
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	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

Mar 5th, 2002, 08:28 PM	#7
Guest Posts: n/a	Oh no, it shows FALSE. So, is there a way to change those "text" numbers to the "real" number

Mar 5th, 2002, 08:40 PM	#8
Paul B Board Regular Join Date: Feb 2002 Location: Georgia USA Posts: 544	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

Mar 5th, 2002, 08:41 PM	#9
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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