Ummmm...Have you checked out the COUNT function.
It counts numbers.

The sum function will treat text cells as zeros so they don't needlessly
corrupt the returned value.

A1: User
A2: =SUM(A1)
returns 0

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"kaz ogonowski" wrote:

While using Logical functions IF, Ive 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 Ive 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
youd 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.

http://www.microsoft.com/office/comm...et.f unctions