if function, sum function problems

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
 
P

Peo Sjoblom

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)
 
B

Bob Phillips

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)
 
G

Guest

Thanks for responding so fast
However your formula does not return the word "Error"
as I would like it to.
 
P

Peo Sjoblom

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)
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top