if function, sum function problems

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a formula in my worksheet

=IF(ISERR(J7+J8+J9+J10+J11),"Error",J7+J8+J9+J10)

it works just fine for adding 4 cells together
and it returns the word "error" if other cells that
add numbers to arrive at J7, J8, etc have errors in them.

My problem is I can't get the formula right to sum all
the cells in column J by using the normal =sum(J7:J60)
approach in the formula above.

Thanks for any help in advance
 
What kind of errors do you get? You can use sumif and trap the error

=SUMIF(J7:J11,"<>#N/A")

will sum the cell without the error, if it is a div error then you better
fix that in the cell that is
returning the error ( if(a2=0,0,a1/a2) will trap a divide by zero error )

Replace #N/A in the formula with the error you get, if you get different
errors use

=SUM(IF(ISNUMBER(J7:J11),J7:J11))

entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Dave,

This works for me

=IF(ISERROR(SUM(J7:J60)),"",SUM(J7:J60))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for responding so fast
However your formula does not return the word "Error"
as I would like it to.
 
Do you want an "error" if there is an error then use

=IF(ISERROR(J7:J11),"error",SUM(J7:J11))


if you want to sum regardless of error use

=SUM(IF(ISNUMBER(J7:J12),J7:J12))

array entered

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Sorry I got cut off, I didn't have time to finish my
response.

There must be another reason it's not working
Would having the word "error" come up in another cell
that this formula is calculating from caused any problems
 
Back
Top