Why is it that cells that seem identical are not counted by the "countif" formula in excel. I have check the formatting of the cells and they seem to be identical. I have to keep double checking my work by filtering my columns. The filter function seems to pick up the right number of cells but the countif doesn't always do this. Why is this and how do I stop it?

"GGG" wrote:
Why is it that cells that seem identical are not counted
by the "countif" formula in excel. I have check the
formatting of the cells and they seem to be identical.

The format does not matter. That only controls the __appearance__ of the
value of a cell. In fact, if A2 is the formula =A1, you can format A1 as
Date and A2 as Number, and COUNTIF will count both, given the correct
parameter.

Two cells can __appear__ to have the same value due to formatting, but their
__actual__ values can be very different. For example, is A1 contains 1.234
and A2 contains 1.230, but might appear the same (1.23) if formatted as
Number with 2 decimal places. But COUNTIF(A1:A2,1.23) will return only 1.

Moreover, cell values can have more precision than Excel will display,
contrary to much misinformation, including documentation from Microsoft. So
even if you format two cells so that they display 15 significant digits (the
most that Excel will format), they might be infinitesimally different.

Two cells might be infinitesimally different even if the formula =(A1=A2)
returns TRUE and =A1-A2 displays 0.00E+00 in Scientific format. This is due
to an ill-advised and inconsistently-applied algorithm in Excel that tries
to "correct" for infinitesimal differences.

The most reliable way to determine if two cells have identical values is the
formula =MATCH(A1,A2,0). If they are indeed identical, MATCH returns 1;
otherwise, MATCH returns a #N/A error.


"GGG" wrote:
I have to keep double checking my work by filtering my
columns. The filter function seems to pick up the right
number of cells but the countif doesn't always do this.
Why is this and how do I stop it?

I would have to see an example Excel file to answer dispositively.

I guess excelbanter.com has a way to attach a zip file that can contain an
example Excel file. (I am not an excelbanter.com user.)

Alternatively, you can upload an example Excel file (devoid of any private
data) that demonstrates the problem to a file-sharing website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...)
in a response here. The following is a list of some free file-sharing
websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

Hi

Have you any unwanted characters in the cells! Have you used the LEN function to see if there are any unwanted characters. Try this ARRAY formula to count: =SUM((TRIM(Your_Range)=Your _criteria)*1). Also CLEAN function is another handy function