Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max sums | Excel Discussion (Misc queries) | |||
sums | Excel Discussion (Misc queries) | |||
SUMS | Excel Discussion (Misc queries) | |||
How to add previous sums in a column to current sums in a column? | Excel Worksheet Functions | |||
Sums | Excel Discussion (Misc queries) |