Ignore #DIVIDE BY ZERO in Sheet Summary SUM formula

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!

KPH

Hi @shelbylund

The IFERROR in your formula means sum the Annual Cost (Original) column and return nothing if doing so results in an error (which it will if there is an error anywhere in that column). It does not mean sum only the rows that don't include errors.

The quickest and cleanest solution would be to remove the DIVIDE BY ZERO errors from the data in the Annual Cost (Original) column. You can do this by

• using an IFERROR function within the calculation that determines the Annual Cost,
• or using an IF in that formula to only do the calculation if the denominator is greater than 0.

Hope that helps.