How do I sum an array of cells, even if some of them are #N/A

[Guest]

I have a table where I lookup working hours in a "Incident reporting sheet"
I want to sum these hours per week. Some of the cells are invalid (#N/A),
because there were no incidents that day. But then I can't get the sum for
those specific weeks. How do I ignore the invalid cells in summing those
arrays?

 

[Leo Heuser]

One way:

=SUM(IF(ISERROR(A2:A100),0,A2:A100))

The formula is an array formula and must be
entered with <Shift><Ctrl><Enter>, also if
edited later.

 

[Aladin Akyurek]

=SUMIF(Range,"<>#N/A")

 

[JMay]

Why doesn't:
=SUMPRODUCT((ISNUMBER(A1:A5))*(A1:A5))
work?
It still produces #N/A
TIA,

 

[Jason Morin]

It doesn't work because you're trying to multiply values
with errors. If A3 were #N/A, you get:

={TRUE;TRUE;FALSE;TRUE;TRUE}*{3;4;#N/A;3;2}

which produces

{3;4;#N/A;3;2}

Trying to perform an operation (add,multiply,etc.) on an
error value only returns another error. Aladin's formula
simply ignores #N/A, while Leo's converts those error
values to 0.

HTH
Jason
Atlanta, GA

 

[Aladin Akyurek]

Adding to Jason's note... That SumProduct formula does not filter out
the #N/A's. Filtering would require a conditional with IF. Adding such a
conditional as a term to a SumProduct formula would force us to confirm
it with control+shift+enter. In such cases, it's better to switch to an
appropriate "array" formula.