COUNTIF Formula for Checkbox Column Not Working in Sheet Summary

I'm trying to create a formula in the Sheet Summary that shows the number of checked items in a Checkbox column named Pending, out of the total number of rows that have either a checked or unchecked box (i.e., excluding blanks).

=COUNTIF([Pending]:[Pending], 1) + "/" + (COUNTIF([Pending]:[Pending], 1) + COUNTIF([Pending]:[Pending], 0))

I’ve verified that:

The column name is correct
The column is defined as a Checkbox type
The summary field is set to Text/Number

Could you please help me understand what’s causing the error, and whether there’s a recommended workaround?

Thanks in advance,
Gaia

Find more posts tagged with

Formulas

Support

Sheet Summary

Accepted answers

All comments

Paul Newcome

Try this instead:

=COUNTIFS([Pending]:[Pending], @cell = 1) + "/" + COUNTIFS([Pending]:[Pending], OR(@cell = 1, @cell <> 1))

Gaia Amaia

thank you but it dosent work ..

Paul Newcome

AAre You getting an error or an incorrect number?

JR90

I think I have it, but kind of in a different way. The checkbox populates when certain cells become not blank, for this specific smartsheet it could be the Test # column or the System column. So you could count the cells that populate the empty check box and make that the denominator.

The formula should look something like this:

=COUNTIF(pending:pending, 1) + "/" + COUNTIF([Test #]:System, NOT(ISBLANK(@cell )))

Please let me know if that works

Thanks,

Gaia Amaia

=COUNTIF(Pending:Pending, 1) + "/" + COUNTIF(Test:System, NOT(ISBLANK(@cell )))

this works - but shows parent row as well, so it doesn't work for me.

any suggestion?

AdamSYNH

Hi @Gaia Amaia,

I think I see what you mean, assuming that the System column is blank for your parent rows and never blank for the child rows you wish to count, this formula should work:

=COUNTIF(Pending:Pending, 1) + "/" + (COUNTIFS(Pending:Pending, 1, System:System, NOT(ISBLANK(@cell ))) + COUNTIFS(Pending:Pending, 0, System:System, NOT(ISBLANK(@cell ))))

Alternatively, if you want to keep the formula dependent on the row being a child of each of your Test # parent rows you could use a helper column:

Add a new column with the formula:

=COUNT(ANCESTORS([Task #]@row))

Summary Field formula:

=COUNTIF(Pending:Pending, 1) + "/" + (COUNTIFS(Pending:Pending, 1, [Your Ancestors helper column name]:[Your Ancestors helper column name], >0) + COUNTIFS(Pending:Pending, 0, [Your Ancestors helper column name]:[Your Ancestors helper column name], >0))

If this doesn't work, could you please provide a little more information on what you do/do not want to be included in your counts?

Paul Newcome

Try this:

=COUNTIFS([Pending]:[Pending], @cell = 1) + "/" + COUNTIFS(System:System, @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.