Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula to count check boxes in row one of column

Lou L
Lou L ✭
edited 12/09/19 in Archived 2016 Posts

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

Tags:
· Share on FacebookShare on Twitter

Comments

  • J. Craig Williams
    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

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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. 

    · Share on FacebookShare on Twitter
  • Lou L
    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!Smile

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    +1 on Great Trick.

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories