COUNTIF formula calculating a checkbox column incorrectly
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
Answers
-
@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.
-
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.
-
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
-
Was this issue ever resolved by Smartsheet Developers? Would like to be able to count unchecked boxes for dashboard and project management. Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!