Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I use istext with a sum formula?
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I use istext with a sum formula?
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 wrote: 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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I use istext with a sum formula?
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. "Chip Pearson" wrote: 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 wrote: 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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I use istext with a sum formula?
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................ "Jamie Thompson" wrote in message ... 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. "Chip Pearson" wrote: 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 wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF AND ISTEXT ISBLANK formula | Excel Worksheet Functions | |||
IF(ISTEXT Concatenate formula question | Excel Worksheet Functions | |||
IF-ISTEXT formula | Excel Discussion (Misc queries) | |||
ISTEXT TRUE then copy formula from cell X | Excel Worksheet Functions | |||
I need help with a =sum IF ISTEXT formula. I keep getting 0. | Excel Discussion (Misc queries) |