Countif function for unchecked box

MattW PDC

Is there a way to run the countif function if a check box is unchecked? I see a command example for when it is checked, but I want to exclude any checked boxes as those would mess up my counts due to the negative value associate when it is checked.

MattW PDC

Looks like I was able to reverse engineer this and determine that using a 0, instead of a 1 allowed the function to work as I intended it.

Genevieve P.

Hi @MattW PDC

I'm glad you were able to figure this out! Yes, 1 represents "checked" in a formula and 0 represents "un-checked". You can also use true and false, but I personally prefer 1 and 0.

Cheers,

Genevieve

Susan van Niekerk

Hi

I tried using the countif for unchecked but the amount coming back to me is wrong.

Have checked to source sheet to make sure

=COUNTIF({Reconcile 2}, false)

Genevieve P.

Hi @Susan van Niekerk

The first thing I'd check here is the {range} to make sure it's pointing at the correct checkbox column. You can also try using 0 instead of false to see if that helps, too:

=COUNTIF({Reconcile 2}, 0)

If you're still seeing an incorrect number, my next test would be to add a filter in your source sheet to compare the number of rows in the filter versus the value that the formula is bringing back. If none of this has helped, it would be useful to see a screen capture of your source sheet, but please block out sensitive data.

Cheers,

Genevieve

Jayy S.

https://community.smartsheet.com/discussion/comment/374755#Comment_374755

I have this same problem, and it's looking like the formula in the Sheet Summary is counting the empty rows that are at the bottom of the sheet as well. When I used the filters to see how many are unchecked, it's showing the correct number.

Paul Newcome

@Jayy S. You can subtract 10 from the final COUNTIFS or you can reference another field in the sheet that will have something in every row aside from those ten at the bottom.

Jayy S.

https://community.smartsheet.com/discussion/comment/448804#Comment_448804

That is absolutely ludicrous to have to create so many workaround when the formula should be counting only unchecked boxes. It's also ridiculous that 10 blank rows are automatically added and clogging up the view.

Paul Newcome

@Jayy S. There isn't the need for "so many" workarounds. It one or the other, and the one is as simple as subtracting 10. I'm also not sure how blank rows are clogging up the view when they are at the bottom of the sheet and blank. It is much less "clogging" than what you would find in programs such as Excel.