how to sum cells and ignore the #div/0! 's ?

[Guest]

I have a column of data that links to another sheet I need a ytd total for.
The problem is that the cells contain formulas (on the feeding sheet) that
return #div/0! until the date has passed and data is entered into its
referring cells.

Is there a way I can sum these columns and ignore the #div/0! 's ? I had
been using a simple a20+a40+a60 type formula.

Thanks,


Todd

 

[JE McGimpsey]

I'd recommend that you fix the #DIV/0s instead - it's generally bad
practice to have "expected errors" on your sheet - it tends to mask or
desensitize one to other problems.

For instance, if you have something like

=A1/B1

where B1 is zero until a certain date, use

=IF(B1>0, A1/B1,"")

You can then use SUM, which ignores text.

 

[Guest]

Only way would be to use

=SUMIF(Range,"<>#DIV/0!")

but better would be to eliminate the errors by using

=IF(B1=0,0,A1/B1)

so if the cell you are dividing with is zero or blank, 0, otherwise your
formula


Regards,

Peo Sjoblom

 

[Jeff]

I have a question too, can someone help me?
My problem is if : =(N35*2+M35) / L35+K35 where L35 = 0 and K35 = 0%, how
should i put it correctly? (both N35 & M35 have a figure)

 

[David Biddulph]

=IF(L35=0,"",(N35*2+M35) / L35+K35)

I've tested only for L35 being 0, not K35, because you are not dividing by
anything involving K35, but as you mention K35 and it is a percentage, I
wonder whether you actually intended not
=(N35*2+M35) / L35+K35
but
=(N35*2+M35) / (L35*(1+K35))
in which case, change my formula to
=IF(L35=0,"",(N35*2+M35) / (L35*(1+K35)))
in which case you're still not worried if K35 is 0, unless L35 is also 0.

If you need to cope with the case where K35 is -100%, you could use
=IF(L35*(1+K35)=0,"",(N35*2+M35) / (L35*(1+K35)))

 

[T. Valko]

What result do you want if L35 = 0 and K35 = 0%?

This will return a blank cell:

=IF(L35+K35=0,"",(N35*2+M35) / L35+K35)

 

[abdul gaffoor]

I have a column of data that links to another sheet I need a ytd total for.
The problem is that the cells contain formulas (on the feeding sheet) that
return #div/0! until the date has passed and data is entered into its
referring cells.

Is there a way I can sum these columns and ignore the #div/0! 's ? I had
been using a simple a20+a40+a60 type formula.

Thanks,


Todd