Sporaticaly I lose the ability to use the count function after ap.

[Susan]

Every month I export a file from access into an excel spreadsheet to
distribute reports. I apply filters to the data for ease in validation.
Sporatically, I lose the ability to use the sum or count function in the
spreadsheet. For instance, most of the time I can filter a colum and it will
tell me in the bottom status bar that there are 20 out of 665 that have the
number one in the column. I have verified that this column format is number,
yet I cannot get this function to work. Any ideas?

 

[Biff]

Hi Susan!

Two possibilities:

Even though the number 1 looks like a number and you still
have the column formatted as NUMBER, it is actually a TEXT
value, or

There may be unseen spaces or other types of unseen
characters in the cells.

How are these cells aligned? Unless you change the default
alignment, text aligns left and numeric numbers align
right.

You can (in most cases) convert text numbers back to
numeric numbers by selecting an empty cell and copying it.
Then select the cells in question and do Paste Special>Add.

You can test for unseen characters by using the LEN()
function. Cell A1 contains a 1 as you described. Try this
formula in any cell: =LEN(A1). If the answer is anything
but 1 then there are unseen characters in cell A1.

You can eliminate unseen SPACES by using the TRIM()
function. This will eliminate LEADING and TRAILING spaces
from a string but not the normal spaces found between
words.

Biff