I have a column of numbers that also contains the letter T. If I use a simple IF(A1>0,...
, the letter T is treated as greater than zero and produces the "true" result. However, if I use Countif(A1:A10, ">0"...
, then the T seems to treated as 0; it does not add to the count. Am I missing something? Is there a general rule about the handling of text when compared to numbers?
1 Answer
The following information is a product of my own experimentation.
The COUNTIF
function is less forgiving of type mismatches than the >
operator. It appears that >
(and <
), like Excel's sort feature, will always sort text as being greater than any number but never equal to any number, not the character's ASCII code, not zero. For example, a column containing 5
, A
, 7
, C
, B
, 2
will be sorted into 2
, 5
, 7
, A
, B
, C
when you sort descending.
COUNTIF
, however, requires that the two things being compared are of a similar type. If they aren't, it doesn't count them. By "similar" I mean things like comparing numbers to currency, which works as expected.
Don't be deceived - COUNTIF
search strings starting with comparison operators like >
don't have the same behavior as the actual operators. Notice that using COUNTIF
to compare text with numbers in any way (whether it be >
, <
, or =
) doesn't result in an addition to the total. Exception: the <>
(not equal) operator always succeeds when the operands are of sufficiently different types. Similar results can be observed with dates and plain text.
So, no. Text is not treated as a zero, but the exact handling of it depends on what you're using.
-
Sorry for delay. Ben N, your reply answers my quesion. I could not find any MS documentation on this point. Thanks.– LouCommented Feb 7, 2016 at 20:16