Easy way to count unchecked boxes in a column

Hi there,

I'm trying to create a reference that counts the number of unchecked boxes in a column from one of my form-filled Smartsheets. I'm trying to use the formula "=COUNTIF([column2]:[column2],=false)", where "[column2]" is my checkbox column.

The problem I'm having is that using this formula counts all pre-generated empty rows in addition to the rows entered by the form. As a result, the formula returns a much higher value than intended.

Is there a way, without touching the form-filled Smartsheet, that I can make this calculation on a seperate sheet. There are no *required fields in the form, so using a column to count the total entries and subtracting 'true' values also would not work.

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

You could use a Created column to track when a form entry is made whether fields are filled out or blank and then use a COUNTIFS to count how many times the box is false but the Created is not blank.

Another option would be to simply subtract 10 from your count. SS auto-generates 10 additional rows below the last used row. Just delete all rows that are not used, refresh the sheet to go ahead and get the auto-generated rows on there, then use

=COUNTIF(.................................) - 10

Suki S

Looking at this discussion I am running to something similar. Where I am trying to get a count of all unchecked boxes using the following countif formula. The information will be on a metric page which will be part of a dashboard.

=countif([Paid:Paid],=False), and I've tried =countif([Paid:Paid],=0) and I keep getting the error message #UNPARSEABLE? What am I doing wrong?

Andrée Starå

Hi Suki,

Try this.

=COUNTIF(Paid:Paid, 0) and place it in another column then the Paid one.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Jed Rusyniak

Tried this, but not working. See attachments.

Screen Shot 2019-11-02 at 8.12.19 AM.png

Screen Shot 2019-11-02 at 8.12.28 AM.png

Andrée Starå

When referencing a column in the same sheet that has Numbers, spaces, or special characters, you'd surround the column name with square brackets [].

Curly Brackets, as in your formula, is used when referencing something on another sheet (cross-sheet).

Try this.

=COUNTIF([IN WN]:[IN WN], 0)

Did it work?

Jed Rusyniak

yes it did. you are a lifesaver ?

Andrée Starå

Excellent!

Happy to help!

Phil Wightman

@Andrée Starå can you help with the above when I want it to count unchecked check boxes only for completed rows. The use of =COUNTIF(Paid:Paid, 0) format counts all unchecked boxes even if the row is blank.

Thanks

Phil

Cody Jorgensen

https://community.smartsheet.com/discussion/comment/186571#Comment_186571

How do I incorporate the Blank formula to for the Created? I can't seem to add that formula to my COUNTIF formula.

Paul Newcome

@Cody Jorgensen The range criteria set in a COUNTIFS would be

......................, [Column Name]:[Column name], @cell <> "", ...................

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.