COUNTIF Formula for Checkbox Column Not Working in Sheet Summary

Gaia Amaia

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

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,

JR

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 <> "")