The others have suggested the count function. Here is another that
literally does what you suggest. It tests for a number and counts only
those cells that evaluate to a number.

One could argue that your problem is caused by the fact that you
are counting numbers and text together without checking to
see if you can really add the "values" in the cells. Certainly one could
argue that since your use of the sum function is "ambiguous"
it might be reasonable that the word "user" should have a value greater
than zero, since the cell will clearly not contain the value of zero
if it contains the value "user".


=SUM(IF(ISNUMBER((B1:B4)),1,0)) (as an array)

Pieter Vandenberg


kaz ogonowski wrote:
: While using Logical functions IF, I've found a problem that I
wish to share
: with Microsoft and other Excel users. Here is a very simple
example.
: Assuming that my data B1: B4 contains numbers and text. B1=2,
B2=5, B3=User,
: B4=7. The main task for formula B5: =SUM(IF(B1:B40,1)) is to
count numbers.
: The result of this formula is 4 but it should be 3. It is because
: unfortunately this formula counts also text which is incorrect.
The logical
: test in this case is evaluated to true for numbers bigger than
zero and
: unfortunately also for any text in cells. As a consequence, the
formula B5
: counts texts the same way as numbers. To avoid this situation a
logical test
: with a condition 0 for data which contains text should be evaluated to
: false (not to true like it is done in Excel). Of course I can also easily
: eliminate this kind of situation by putting an additional condition but the
: point is to eliminate it overall the way I've suggested above. Actually
: Function IF works that way if any text is assigned to a condition < 0 or
: equal zero (Logical test in this case is evaluated to false). I think that
: you'd agree with me that if logical test with condition for example
User 0
: in the following formula ( the result of this formula is 2) =IF("User"0,2)
: is evaluated to true looks a little awkward and beyond that you can see
: consequences of creating other formulas.
: Thanks for your time.

:


: ----------------
: This post is a suggestion for Microsoft, and Microsoft responds to the
: suggestions with the most votes. To vote for this suggestion, click the "I
: Agree" button in the message pane. If you do not see the button, follow this
: link to open the suggestion in the Microsoft Web-based Newsreader and then
: click "I Agree" in the message pane.