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

[Todd]

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.



In article <[email protected]>,
"Todd" <[email protected]> wrote:

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

[Peo Sjoblom]

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

"Todd" wrote:

> 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

[dastgir]

Luckily today i face the same problem, accidentally i simply type sumif(range) formula and hurray it satrt working without putting any further criteria.