Summing a range that contains errors

  • Thread starter Thread starter Hawk
  • Start date Start date
H

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
 
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
 
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?
 
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...
 
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

.
Click to expand...
 
Back
Top