Excel - #value! error in sum function

Asked By Art on 24-Sep-09 05:11 PM
I need to sum a range of cells.  Some of the cells may contain text.  If so,
the function returns #value!.  In Lotus 1-2-3, text is treated as 0 for
function purposes.  Is there some way to force Excel to do the same, or
another way to get around this problem?




T. Valko replied on 24-Sep-09 05:34 PM
Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

--
Biff
Microsoft Excel MVP
Lancelot replied on 12-Oct-09 06:26 AM
I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I understood.

Unfortunately, =sum does not work when several non-contiguous cells are added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition
not having been met in C1). The =sum formula shows #VALUE! only because the

What do I have to put in my C1 formula so that, when the condition there is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not
allowed to show zero because that is an incorrect value.

What is the best solution?
T. Valko replied on 12-Oct-09 12:25 PM
Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

--
Biff
Microsoft Excel MVP
Lancelot replied on 15-Oct-09 11:55 AM
Thank you very much T.

Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet.
However there are some cases where I have difficulty to know where to put
the COUNT(). They look like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

All the mentioned cells in row 56 could contain "".

Could you help me with this case? I tried to put in one or two COUNT()s but
I could not get it right.

By the way, how would I design the formulas if I wrote the Excel spreadsheet
from scratch. Would I really need so many COUNT()s in the formulas?
Lancelot replied on 15-Oct-09 12:34 PM
Thank you very much T.

With your COUNT() solution I have eliminated most of the #VALUE!s from my
worksheet. However there is one remaining which I cannot get right. It looks
like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

Any of the cells in row 56 can and do contain "".

I tried to insert some COUNT()s in the formula, but it was not successful.
Could you spare a moment to help me with this one?

By the way, I wonder how I would formulate the Excel worksheet if I was
designing it from scratch. Would I really need to have all these COUNT()s in
the formulas - what do other users do?
T. Valko replied on 15-Oct-09 12:37 PM
Here is how you end up getting a #VALUE! error...

A1 = a formula that might return either a number or a formula blank "".
A2 = same thing

If either A1 or A2 contain the formula blank then:

=A1+A2 will return #VALUE!

Excel is telling you that you cannot add TEXT values to numeric values.

In these cases use the SUM function. SUM will ignore any text entries which
is what a formula blank is.

=SUM(A1:A2)

So, with this formula:


Try it like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56;CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0)))

--
Biff
Microsoft Excel MVP
Lancelot replied on 23-Oct-09 05:23 AM
Thank you T.

Your formula solved my problem.
I only had to adapt it, so that where there is a condition with a value 0, I
had to change the 0 to "" in some cases, because the conditioning cell may
contain "".

P.S. Ignore my duplicated posting of the 10/15/2009 - there was a connection
problem on that day and I do not know how to remove or change my repeat
posting.

Thanks again,
Lancelot
T. Valko replied on 23-Oct-09 11:54 AM
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP