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

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

"shat" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> shat

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

/Fredrik

=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


"shat" <[email protected]> wrote in message
news:[email protected]...
>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
> --
> shat

And even shorter:

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


"Fredrik Wahlgren" wrote:

>
> "shat" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > --
> > shat
>
> =IF(V4=0,IF(D4=0,0,SUM(E4/D4*X4)),SUM(W4/V4*X4))
>
> /Fredrik
>
>
>

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.

"shat" wrote:

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

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

shat wrote:
> 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

Thx boss

"Aladin Akyurek" wrote:

> =SUMIF(Y4:Y204,"<>#DIV/0!")
>
> shat wrote:
> > 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
>