"ראובן" wrote:

1. I'm sorry. count and counta gave the same result (I can send the sample
to you if you wish).

In all versions of Excel, if A1:A2 appears to contain 200 and 100 but
SUM(A1:A2) returns 100, then COUNT(A1:A2) will return 1 but COUNTA(A1:A2)
will return 2. What you did is to get the results you claim is a mystery.
If you post your e-mail address, I will e-mail you privately.

2. The inconsistency is that excel does not treat some value in the same way:
If you multiply "abc" by 2 you get #Value. In my case Sum (200, 100) was 100
(ISNUMBER returned false on the 200) but 200*2 was 400
So 200 was numeric and non numeric at the same time. How would you call it?

For bivariate math operations, Excel consistently tries to coerce values
into numbers. Thus ="abc"*2 will return #VALUE and ="200"*2 will return 400
because "200" can be coerced into a number and "abc" cannot. For range
functions, such as SUM, excel coerces nothing; if you have a text string of
digits in a cell, they will be ignored.

A simple way to use this to prep your data is to place a zero in a blank
cell, copy that cell then select the imported data range and Edit|Paste
Special|Add. All text "numbers" will be coerced to numeric values; all
non-coerceable text will become error values. After taking note of the error
locations, you can undo and then repeat the process excluding the
non-coerceable cells if you want to leave them as imported.

Jerry