COUNTIF formula calculating a checkbox column incorrectly

Meyer Scholtz

Hi there

There is most likely an easy fix to my issue, but I just can't crack it myself:

I have two COUNTIFS formulas to calculate how many checkboxes are ticked and how many are unticked.

The COUNTIFS formula for the ticked checkboxes is working 100% - there are 5 check boxes ticked, which I counted manually to confirm and the formula gives = 5. However, the COUNTIFS formula for the unticked checkboxes is not counting the correct number.

In below screenshot, there are 67 rows. 5 of which, are ticked checkboxes. The COUNTIFS formula therefor needs to count 62 for the unchecked boxes, but it's calculating 72??

The Red circle on the left indicates the row number & the Red circle on the right is what the COUNTIFS formula is counting.

The Green circle on the right is the COUNTIFS formula that correctly calculates the 5 ticked checkboxes.

My two COUNTIFS formulas are below:

=COUNTIFS([X6 issue]:[X6 issue], 0) - for the unticked checkboxes

=COUNTIFS([X6 issue]:[X6 issue], 1) - for the ticked checkboxes

Neil Watson

@Meyer Scholtz the issue is that the formula is including empty rows below (from 67 onwards). I was able to create the same issue with the sheet below, you can see that the formula is including rows 17 - 26. Even when I delete these rows, they reappear again. I am not sure if this is a bug.

Meyer Scholtz

Thank you for the confirmation @Neil Watson. Thought I was doing something wrong.

I fixed the formula by adding a simple minus to the formula: =COUNT([Vehicle title]:[Vehicle title]) - [X6 Issues Count]#.

Can't wait until it gets fixed, if it indeed is a bug or something.

Neil Watson

Indeed this appears to be the only solution - see response from Smartsheet support below:

Currently we do not have the option to avoid adding 10 new rows automatically when the sheet is refreshed, but I've let our Product Team. 

If you would like to use the COUNTIF and not count the 10 new blank rows, you can subtract 10 from the formula which would look as follows:

• =COUNTIF([Column2]:[Column2], 0) - 10

Jolen

Was this issue ever resolved by Smartsheet Developers? Would like to be able to count unchecked boxes for dashboard and project management. Thanks