Summing a range that contains errors

[Hawk]

I am trying to sum a range of cells (A1:A5). The cells
will contain one of the following values:

0
1
#Value!

For example:
A1 - 1
A2 - 1
A3 - 0
A4 - 1
A5 - #Value!

=sum(A1:A5) returns the following:

#Value!

I would like it to return 3...

I'm sure there is a simple solution, but I cannot seem to
figure one out. TIA

 

[Jason Morin]

One way:

=SUMIF(A1:A5,"<>#VALUE!")

For all error values:

=SUM(IF(ISNUMBER(A1:A5),A1:A5))

Second formula is array-entered.

HTH
Jason
Atlanta, GA

 

[J.E. McGimpsey]

Jason gave you a good solution for what you asked, but a better
solution, IMNSHO, would be to eliminate the error condition. What's
causing the #VALUE! error?

 

[Hawk]

Jason's will work fine for my situation. I can eliminate
the error, but I am modifying someone else's workbook and
I am looking for the most bang with the fewest changes.
Thanks for the input...

 

[Hawk]

Thanks...that should work nicely.

-----Original Message-----
One way:

=SUMIF(A1:A5,"<>#VALUE!")

For all error values:

=SUM(IF(ISNUMBER(A1:A5),A1:A5))

Second formula is array-entered.

HTH
Jason
Atlanta, GA

.