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.
1
