Why is SUMIF returning #DIVIDE BY ZERO error?

Hannah H

Hello,

I have written a simple SUMIF formula to sum the parent rows in the Sheet Summary of a budget sheet.

Formula is written as =SUMIF(Parent:Parent, 1, [Budget at Completion]:[Budget at Completion])

It is giving me a #DIVIDE BY ZERO error, and I cannot figure out why. I don't have division in my formula. The data is formatted to USD in both the sheet and the summary.

Any ideas? Image attached.

Thanks,

Hannah

Mike Wilday

That is so strange. I would reach out to Smartsheet support to request assistance.

Hannah H

Thanks @Mike Wilday, I'm glad I'm not missing something glaringly obvious here! I'll reach out to Support.

Mark Cronk

Hi @Hannah H ,

The #Divide by Zero error doesn't make sense but, do a scan of your budget at completion column to confirm that it only contains numbers. If there's a cell with text you'll get an error. Is the budget at completion a formula? If it is, use an IFERROR statement that return a 0 rather than a space or other character.

Any luck?

Mark

Mike Wilday

@hannah H Here's more information on the Iferror formula so you can implement it if you are doing a formula to gather your budget.

https://help.smartsheet.com/function/iferror

Mike Wilday

I also did some some testing. Definitely check your budget column. I found that if I had a Divide by Zero error in the budget column it returned the error in my sumifs statement. Definitely use the IFERROR to fix any errors that could appear in your budget column.

2020-12-04_15-02-07 (1).gif