The Countif bug (and how to avoid it)

The Countif function counts the number of cells in a range that match some criterion.  If we enter 1,2,3 in cells A1:A3 and enter =COUNTIF($A$1:$A$3,A1) somewhere else, it will return 1:

But if we enter three text strings with 16 or more numerical characters, that are only different in the 16th or later characters, COUNTIF will say they are all the same:

This problem was reported by John Walkenbach at Daily Dose of Excel, back in 2006.  The solution given then used the SUM function as an array function: =SUM((A1:A3=A1)*1).  The alternative shown below gives the same results.  Note that using SUM the function must be entered as an array, using Ctrl-Shift-Enter.

An alternative that does not require array function entry is to use the SUMPRODUCT function:

But after 11 years Lori Miller returned to Daily Dose of Excel with a way to get COUNTIF to work correctly.  Precede the address of the criterion cell with CHAR(173)&:

CHAR(173) is a “soft hyphen” character, which will ensure that the contents of the data range are treated as text strings, rather than numbers, but is otherwise ignored.  Now all numeric text strings of any length may be entered, and they will only be counted as being the same if they really are.

This entry was posted in Computing - general, Excel and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s