letters and numbers

[coltheplumb]

Hi ho again heres another prob for you i have a list of numbers and letters all in different cells is there a way at the bottom of the list to just count the number of cell with letters in and ignore the cells with numbers in ?, ie if the list is like this each item in a different cell....1 d n 3 4 k k ..and so on so the total would be 4... hope i have explained that ok ?
Thankx

Colin X

 

[Jonmo1]

Try

=COUNTA(A1:A10)-COUNT(A1:A10)

COUNTA = Count of NON Blank cells (text or number)
COUNT = Count of Numbers only
So COUNTA-COUNT = Count of Text (non number)

Hope that helps.

 

[coltheplumb]

Na that dont work it still counting numbers or letters ??

Colin X

 

[Aladin Akyurek]

Na that dont work it still counting numbers or letters ??

Colin X

If you just want to count text-valued, non-blank, cells...

=COUNTIF(A1:A7,"?*")

 

[coltheplumb]

Na that still counts letters and numbers the same ... i only want it to count any cell with a letter in it and to ignore blank cells or cells with numbers in them

Colin X

 

[Joe4]

Na that dont work it still counting numbers or letters ??

They both work, unless your numerical values are really entered/formatted as "Text" instead of "Numbers".

An easy way to check is using the ISNUMBER function.
Let' say that you have 1 entered in cell A1.
What does =ISNUMBER(A1) return?
If it returns FALSE, you have a text entry, not a numeric one.

You can use Text-to-Columns to correct that.

 

[coltheplumb]

ok so i have done the =isnumber thing and it does return False.. so as a novice how do i change it to the text-to-columns thing ??

Colin X

 

[Aladin Akyurek]

ok so i have done the =isnumber thing and it does return False.. so as a novice how do i change it to the text-to-columns thing ??

Colin X

Did you try post #4?

 

[Ron Coderre]

If you want the count of numeric and numeric-text cells, try this regular formula
=COUNT(INDEX(1*(A1:A10&""),0))

And for the count of all other non-blank, non-numeric cells
=SUMPRODUCT(--ISERROR(INDEX(1/(A1:A10&1),0)))

Does that help?

 

[Joe4]

ok so i have done the =isnumber thing and it does return False.. so as a novice how do i change it to the text-to-columns thing ??

Highlight your column of data, select "Text to Columns" from the Data menu/ribbon. Click "Finish". This should convert your numbers to number format. Then those solutions will work.

Did you try post #4?

Aladin,
I tried your solution when the numerical values are entered as numbers, and it didn't work for me either until those entries were converted to a numeric format.