Menu
I am having problems with the CountIf Function in Excel.
=COUNTIF(A:A,A2)
The A column consists of these items:
0107791489614255200011140926107503100513
0107791489614255200011140926107503100457
0107791489614255200011140926107503100518
0107791489614255200011140926107503100503
0107791489614255200011140926107503100519
0107791489614255200011140926107503100444
0107791489614255200011140926107503100521
0107791489614255200011140926107503100438
0107791489614255200011140926107503100449
0107791489614255200011140926107503100443
0107791489614255200011140926107503100501
0107791489614255200011140926107503100455
the formula results to 12, even though these set of strings are not really the same at all. It counts these strings as similar strings, I am thinking this is related to its string length?
What do you guys think? I appreciate your help.
811
COUNTIFS Not Working for Incorrect Range Reference When we use more than one criteria in the COUNTIFS function, the range of cells for different criteria must have the same number of cells. Otherwise, the COUNTIF function won't work.
Solution: Open the linked workbook indicated in the formula, and press F9 to refresh the formula. You can also work around this issue by using SUM and IF functions together in an array formula. See SUMIF, COUNTIF and COUNTBLANK functions return #VALUE!
This is the result from the original formula which returned a "0" result. This is the corrected result obtained by changing the formula in the source cell to show a true text result. This is the corrected result obtained by using the asterixes in the COUNTIF formula to 'force' the recognition of the < as text.
Most often, a #SPILL! error occurs when a spill range is blocked by something on the worksheet, and the solution is to clear the spill range of any obstructing data.
+1, A good question. Not really a bug but a feature!
This is due to Excel implicitly converting the inputs to its internal numeric type and losing precision in doing so. Excel's internal numeric type is an IEEE floating point double precision number. (Although it does clever things with formatting and error propagation so it appears to get sums like 1/3 + 1/3 + 1/3 correct).
As they are so similar they all compare as mutually equal.
One remedy would be to prefix each string with ' (single quotation) which will prevent the conversion to the numeric type. Then the COUNTIF value returns 1. (At least in my version of Excel; 2013).
56
Preceding the strings with a single apostrophe will not remedy the situation. COUNTIF is designed to interpret data as numerical, where possible, irrespective of the datatype of the values in question. This is sometimes helpful, sometimes (as here) not.
SUMPRODUCT does not exhibit this property:
=SUMPRODUCT(0+($A$1:$A$12=A2))
will return 1, as desired.
Regards
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
