How do I use istext with a sum formula?

[Jamie Thompson]

I have to add three cells together to get a total. The second cell could
possible have text in it. How do I use the istext formula?

 

[Chip Pearson]

The SUM function ignores text values, so you could use

=SUM(A1:C1)
or
=SUM(A1,B1,C1)

Both formulas will ignore any non-numeric values. However, if a cell
has an error in it (e.g., #VALUE), SUM will return that error. To sum
a range that has error values, use

=SUM(IF(ISERROR(A2:C2),0,A2:C2))

Since this is an array formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. The formula will not
work properly if it is not entered as an array formula. For
much more information about array formulas, see
http://www.cpearson.com/Excel/ArrayFormulas.aspx.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 8 Dec 2008 12:31:01 -0800, Jamie Thompson <Jamie

 

[Jamie Thompson]

I'm taking a class on Excel and I have to use the ISTEXT formula to get the
correct result. I have to add cells O27:029. Cell O28 uses nested if's to
come up with
a "shipping cost" depending on what state the items are being shipped to.
Two of the states say "No Cost". How would I use the ISTEXT formula with my
sum formula? My instructions say to "use help-look at istext". This is the
first time we are using this function so I don't have a clue how to put the
formula's together.

 

[Ken Wright]

If you really have to use it then you could use something like:-

=SUM(O27,IF(ISTEXT(O28),0,O28),O29)

or perhaps

=IF(ISTEXT(O28),SUM(O27,O29),SUM(O27:O29)) - (Note the different
separators in the two SUMS)

but if you really want the brownie points in the class, you would be better
off understanding the difference between this solution and Chip's, give them
what they have asked for by using the examples above, and then use Chip's
solution to show why the ISTEXT function is really rather superfluous in
this example.

Regards
Ken................