Formula to count check boxes in row one of column

Lou L

I have a questionnaire with multiple columns.  Responses are either check boxes or comments.  I want to summarise the total check boxes in each column.  I can add a column and write a formula to total the checkboxes, but the error message "#1 or 0 expected" appears when the formula is in the same column as the check boxes. (sounds like the formula need to have an answer of true or false.)  I didn't want to double my # of columns.

Any easy way to tally a column of check boxes?

Used "=COUNTIF([Being aware and informed about it?]2:[Being aware and informed about it?]35, 1)"  There are now only 354 responses, but hope for several hundred more.

Should I be using a report for this?

Thanks

J. Craig Williams

Lou,

 

Yes, the Checkbox is expecting either a 0 (unchecked) or 1 (checked).

If you use a COUNTIF there and the result is 2, it is wrong and tells you so.

(unlike some programming languages where 0 is false and everything else is true)

 

You'll need to move that formula to a different place.

I usuallly pick one of the text/number columns and have a header at the top of the sheet for these sorts of counts and summaries.

 

One of the features of Smartsheet that you'll need to grok if you do a lot of formulas is that each column has a type - and that type dictates what you can and can not do there. This adds some amazingly easy functionality, but is jarring when first trying things (at least it was for me coming in as an advanced Excel and MS Project user).

 

Hope this helps.

 

Craig

Travis

The checkbox cell is expecting the formula result to be a 1 or a 0 but it is possible to have any formula located in the checkbox cell. The trick is to format the formula result as a text string by concatenating a text value.

 

Try this:

 

=COUNTIF([Being aware and informed about it?]2:[Being aware and informed about it?]35, 1) + ""

 

The + "" at the end will format the formula result as a text string and will then work in a checkbox cell. 

Lou L

Travis,

Thanks so much, I just got totals in all columns in under a minute without doubling my # of columns.  Thanks for the help!

Great trick! Love it!

J. Craig Williams

+1 on Great Trick.