Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
I need to show a total for a series of values.
Some of these values may be replaced with "N/A" as a result of an IF formula. When this happens, the total does not always calculate, which has a knock-on effect for other formulas. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
Try this array formula
=SUM(IF(ISNA(A1:A8),0,A1:A8)) remember to complete it with SHIFT+CTRL+ENTER not just ENTER best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Robojohn" wrote in message ... I need to show a total for a series of values. Some of these values may be replaced with "N/A" as a result of an IF formula. When this happens, the total does not always calculate, which has a knock-on effect for other formulas. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
Neither AutoSum nor the SUM function ignores errors. If there is an error in
a cell, AutoSum will not reference cells above that cell. The SUM function will return the same error as exists in any of its input cells. To SUM a range and ignore errors, use the following array formula =SUM(IF(ISNUMBER(A1:A10),A1:A10)) This will return the SUM of A1:A10, ignoring error values. Change both instances of "A1:A10" to your range. 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 { }. For more info about array formulas, see www.cpearson.com/excel/array.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Robojohn" wrote in message ... I need to show a total for a series of values. Some of these values may be replaced with "N/A" as a result of an IF formula. When this happens, the total does not always calculate, which has a knock-on effect for other formulas. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
To SUM a range and ignore errors, use the following array formula
=SUM(IF(ISNUMBER(A1:A10),A1:A10)) This will return the SUM of A1:A10, ignoring error values. Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the range. Rick |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
You sure?
It worked fine for me. "Rick Rothstein (MVP - VB)" wrote: To SUM a range and ignore errors, use the following array formula =SUM(IF(ISNUMBER(A1:A10),A1:A10)) This will return the SUM of A1:A10, ignoring error values. Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the range. Rick -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
Did you try? Chip's formula works for me with DIV0, VALUE and NUM errors.
So does this more long-winded one =SUM(IF(ISERROR(A1:A10),0,A1:A10)) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Rick Rothstein (MVP - VB)" wrote in message ... To SUM a range and ignore errors, use the following array formula =SUM(IF(ISNUMBER(A1:A10),A1:A10)) This will return the SUM of A1:A10, ignoring error values. Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the range. Rick |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
Yes, I tried it before posting and it repeated the error for its result.
Just tried it again... it still doesn't work. Using Excel 2003 on Vista Ultimate in case that makes a difference (although I don't see how it would). Rick "Dave Peterson" wrote in message ... You sure? It worked fine for me. "Rick Rothstein (MVP - VB)" wrote: To SUM a range and ignore errors, use the following array formula =SUM(IF(ISNUMBER(A1:A10),A1:A10)) This will return the SUM of A1:A10, ignoring error values. Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the range. Rick -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
You must do something wrong, did you array enter it?
-- Regards, Peo Sjoblom "Rick Rothstein (MVP - VB)" wrote in message ... Yes, I tried it before posting and it repeated the error for its result. Just tried it again... it still doesn't work. Using Excel 2003 on Vista Ultimate in case that makes a difference (although I don't see how it would). Rick |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Sum not working
Sigh! Never mind... I had forgotten to use Ctrl+Shift+Enter when entering
the formula. What fooled me is that the formula worked fine using just a plain Enter key when the range contained either numbers or text entries... however the plain Enter key method of entering the formula did not survive the error condition. Sorry for the confusion. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Yes, I tried it before posting and it repeated the error for its result. Just tried it again... it still doesn't work. Using Excel 2003 on Vista Ultimate in case that makes a difference (although I don't see how it would). Rick "Dave Peterson" wrote in message ... You sure? It worked fine for me. "Rick Rothstein (MVP - VB)" wrote: To SUM a range and ignore errors, use the following array formula =SUM(IF(ISNUMBER(A1:A10),A1:A10)) This will return the SUM of A1:A10, ignoring error values. Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the range. Rick -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Filter not working | Excel Discussion (Misc queries) | |||
Auto Calc not working | Excel Worksheet Functions | |||
Auto Calc not working | Excel Worksheet Functions | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions | |||
Auto-Calculation Not Working | Excel Discussion (Misc queries) |