Sums including #NAME?

[Fred Holmes]

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

 

[Shane Devenshire]

Hi,

Here's one way

=SUMIF(E12:E15,"<>#NAME?")

 

[Fred Holmes]

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