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

[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

 

[Fredrik Wahlgren]

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

 

[Duke Carey]

And even shorter:

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

 

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

 

[Aladin Akyurek]

=SUMIF(Y4:Y204,"<>#DIV/0!")

 

[AG]

Thx boss

=SUMIF(Y4:Y204,"<>#DIV/0!")