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

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?

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.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"René" skrev i en meddelelse
...
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?

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

René wrote:
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?

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


"Aladin Akyurek" wrote in message
...
=SUMIF(Range,"<#N/A")

René wrote:
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?

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

=3D{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=20
error value only returns another error. Aladin's formula=20
simply ignores #N/A, while Leo's converts those error=20
values to 0.

HTH
Jason
Atlanta, GA

-----Original Message-----
Why doesn't:
=3DSUMPRODUCT((ISNUMBER(A1:A5))*(A1:A5))
work?
It still produces #N/A
TIA,


"Aladin Akyurek" wrote in message
...
=3DSUMIF(Range,"<#N/A")

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


.

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.

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


"Aladin Akyurek" wrote in message
...

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

René wrote:

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?