How do i make a sum formula ignore #div/0! errors in the range

S

shat

i have a colomn where each line has uses the formula
=IF(V4=0,SUM(E4/D4*X4),SUM(W4/V4*X4))
at the bottom i am trying to sum the answers
=sum(y4:y204) but some of the lines return #div/0! because there is no
data on that line.

?How do you tell the sum to exclude the #div/0! errors and sum the others to
get a total????

thanks in advance
 
F

Fredrik Wahlgren

shat said:
i have a colomn where each line has uses the formula
=IF(V4=0,SUM(E4/D4*X4),SUM(W4/V4*X4))
at the bottom i am trying to sum the answers
=sum(y4:y204) but some of the lines return #div/0! because there is no
data on that line.

?How do you tell the sum to exclude the #div/0! errors and sum the others to
get a total????

thanks in advance

=IF(V4=0,IF(D4=0,0,SUM(E4/D4*X4)),SUM(W4/V4*X4))

/Fredrik
 
N

N Harkawat

=SUM(IF(NOT(ISERROR(y4:y204)),y4:y204))
array entered (ctrl+shift+enter)
will exclude all entries which have any errors (N/A#,Div# etc) and sum the
rest
 
F

frankt

A better solution is to avoid the situation altogether. Modify the formulas
to look like this:

=IF(V4=0,if(D4=0,0,E4/D4*X4),if(V4=0,0,W4/V4*X4))

Now there will not be any #DIV0 and you can use a normal SUM.
 

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