Countifs Statement Returning Divide by Zero Error

Donna T

I have a grid with a column of checkboxes ("Prj") and a status column (Status).

The formula was set to:

=countifs(prj:prj,1,status:status,"Complete")

It had been working I made some edits to the sheet and now I am getting a #DIVIDE BY ZERO error. The formula still works on all my other project schedules, so I know it has to be something on this grid. I did go through and make sure I had no blank rows.

If I just do a =count(prj:prj.1) it returns the count, but once I change it to countifs and add the second criteria I get the error.

My objective was to count all the completed projects on the grid.

I've probably been looking at it too long. Any insight would be appreciated.

Thank you,

Donna

Andrée Starå

@Donna T

I sent you an email about that the error is at row 568. If you fix that, it will work.

Make sense?

Let me know if you need additional help with fixing that error or anything else!

Bassam Khalil

Hi @Donna T

Hope you are fine, Could you please add a screenshot (after removing any sensitive data ). And what is the editing you did so you start getting this error.

Andrée Starå

Hi @Donna T

I hope you're well and safe!

What did you edit?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Donna T

Sure, here's a screenshot

The editing I did was changing/adding/deleting rows and checkboxes for the new rows added. I'm used to seeing this divide by zero error wehn I'm averaging children and there is a blank cell present, but here I'm just using =countifs(prj:prj,1,status:status,"Complete").

I know I'm overlooking something! :)

Thanks for your expertise.

Bassam Khalil

@Donna T 

Please try the following

=iferror(countifs(prj:prj,1,status:status,@cell="Complete"),"")

Donna T

Thank you -- unfortunately it just returned a blank cell.

Andrée Starå

@Donna T

Happy to help!

Have you checked so there are no errors in any of the columns (PRJ, Status)?

Donna T

So the checkboxes are manually checked (not populated by a formula). As for the Status column, there is no error codes in any of the sheet's cells in that column. I have 12 other sheets based on this same template -- it's just this one that the formula is failing on. So strange!

Andrée Starå

@Donna T

Strange!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Donna T

@Andrée Starå just shared with you. Thanks!

Andrée Starå

@Donna T

Excellent!

Glad we got it working!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Donna T

I am confused; I don't see the solution.

Andrée Starå

@Donna T

I sent you an email about that the error is at row 568. If you fix that, it will work.

Make sense?

Let me know if you need additional help with fixing that error or anything else!

Donna T

Good grief! If you knew how many times I looked for errors in every line of this sheet?!?!

Thank you -- and sorry you served as a proofreader in this instance! I think you are great!

Thank you for your time

Andrée Starå

@Donna T

Haha! No worries!

You're more than welcome, and thanks for the kind words!

Pro-tip. You can use the Filter feature to see all the options, and if there is a visible error, it will show up.