How to ignore #DIVIDE BY ZERO in SUM function?

shelbylund

I have a formula in my template Sheet Summary that I need to add the dollar amounts in the Annual Cost (Original) column. However, some of the rows will generate #DIVIDE BY ZERO. I need this formula to add only the cells that have a dollar value in them and to ignore the #DIVIDE BY ZERO.

The formula I currently have in the Sheet Summary is:

=IFERROR(SUM([Annual Cost (Original)]:[Annual Cost (Original)]), " ")

This formula is not returning an error but is instead leaving the Sheet Summary field blank.

Can anyone advise on how to fix this? It's not feasible for me to modify the formula in each Sheet Summary as sheets are created from the template to include only the applicable lines.

Thank you!

Jgorsich

You are getting a blank because of your iferror() function - sum() will throw an error if you have an error in the range it is summing, so it is showing the single space you are calling for.

The only way I know of to fix it is to modify the template so that the formulas in Annual Cost (Original) include an iferror() function directly, so that lines that used to show "#Divide by zero" are now blank. Then you sheet summary will work.

shelbylund

@Jgorsich Thank you! I was able to fix it by changing the formula in the Annual Cost (Original) column like you suggested.

Jgorsich

Excellent, glad I could help!