If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sums including #NAME?
Excel 2000
How do I get a SUM() formula to ignore (disregard) any #NAME? errors in the range being summed, and produce (evaluate to) the sum of the valid cells in the range? I have a two-sheet workbook. Sheet1 is a "Report," something handed to members of the board of the organization. Sheet2 is a "Datasheet," produced (exported) by an accounting program in a format that is not very understandable. The Report sheet gets its data by links from the Datasheet, using Named Ranges (cells) in the Datasheet. The accounting package, in generating the datasheet, suppresses zeros, i.e., data elements whose value is zero are not generated at all, do not appear in the data list. So I get a #NAME? error in the cell in the Report where that data element is linked. That #NAME? error in the cell propagates to formulae (sums, which are subtotals and totals), which then also produce an #NAME? error value. How do I get the sum formulae to ignore the #NAME? errors, evaluating them to be zero, and calculating the sum of the values that are good? TIA Fred Holmes |
#2
|
|||
|
|||
Sums including #NAME?
Hi,
Here's one way =SUMIF(E12:E15,"#NAME?") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fred Holmes" wrote: Excel 2000 How do I get a SUM() formula to ignore (disregard) any #NAME? errors in the range being summed, and produce (evaluate to) the sum of the valid cells in the range? I have a two-sheet workbook. Sheet1 is a "Report," something handed to members of the board of the organization. Sheet2 is a "Datasheet," produced (exported) by an accounting program in a format that is not very understandable. The Report sheet gets its data by links from the Datasheet, using Named Ranges (cells) in the Datasheet. The accounting package, in generating the datasheet, suppresses zeros, i.e., data elements whose value is zero are not generated at all, do not appear in the data list. So I get a #NAME? error in the cell in the Report where that data element is linked. That #NAME? error in the cell propagates to formulae (sums, which are subtotals and totals), which then also produce an #NAME? error value. How do I get the sum formulae to ignore the #NAME? errors, evaluating them to be zero, and calculating the sum of the values that are good? TIA Fred Holmes |
#3
|
|||
|
|||
Sums including #NAME?
Definitely "Yes," but there is no "Yes" button in/on my Forte Agent
newsreader. I guess the "Yes" button appears on the web interface? Fred Holmes On Thu, 11 Jun 2009 20:38:01 -0700, Shane Devenshire wrote: Hi, Here's one way =SUMIF(E12:E15,"#NAME?") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fred Holmes" wrote: Excel 2000 How do I get a SUM() formula to ignore (disregard) any #NAME? errors in the range being summed, and produce (evaluate to) the sum of the valid cells in the range? I have a two-sheet workbook. Sheet1 is a "Report," something handed to members of the board of the organization. Sheet2 is a "Datasheet," produced (exported) by an accounting program in a format that is not very understandable. The Report sheet gets its data by links from the Datasheet, using Named Ranges (cells) in the Datasheet. The accounting package, in generating the datasheet, suppresses zeros, i.e., data elements whose value is zero are not generated at all, do not appear in the data list. So I get a #NAME? error in the cell in the Report where that data element is linked. That #NAME? error in the cell propagates to formulae (sums, which are subtotals and totals), which then also produce an #NAME? error value. How do I get the sum formulae to ignore the #NAME? errors, evaluating them to be zero, and calculating the sum of the values that are good? TIA Fred Holmes |
Thread Tools | |
Display Modes | |
|
|