Formula to Count Check Boxes in a Check Box Column

Trying to count in coumns how many boaxes are checked per column - Keep getting Boolean Expected. There are the formulas I have tried:

=COUNTIFS([Mike M]5:[Mike M]135, 1)

=COUNTIF([Mike M]5:[Mike M]135, 1)


Is it possible to created this type of formula?


Best Answer

«1

Answers

  • alexis.ray89371
    alexis.ray89371 ✭✭✭✭✭

    Hi!

    I am having a similar issue and tried your suggestion, but it didn't work for me. I keep getting "Invalid Operation". I'm just trying to count the number of checkboxes that are checked for a column. I tried all the below. Not sure if it is worth noting, but I do have formulas in the all the child cells (not in the parent cells) that automatically check the box for me based on a certain criterion. The formula in the child cells is =IF([Task Owner]@row <> [Technology Owner]@row, IF(Status@row <> "Completed", IF(Status@row <> "N/A", IF(Status@row <> "X", IF([Task Owner]@row = "Rob Rogers", 1, "")))))

    Thank you for any guidance you can provide!

    =COUNTIF([Rob task vs tech owner]1:[Rob task vs tech owner]987, 1) + ""

    =COUNTIF([Rob task vs tech owner]:[Rob task vs tech owner], 1) + ""

    =COUNTIF([Rob task vs tech owner]:[Rob task vs tech owner], 1)

    =COUNTIF([Rob task vs tech owner]1:[Rob task vs tech owner]987, 1)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @alexis.ray89371

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Any insight into how you might do with with multiple criteria? For example, I need to count if a box is checked AND a specific reason code. Is this a countif statement and if so, how should it be structured?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Kasey Bernard

    Hi Kasey,

    I'd recommend using the COUNTIFS function.

    Try something like this.

    =COUNTIFS(Checkbox:Checkbox; 1; [Reason Code]:[Reason Code]; "1122")

    The same version but with the below changes for your and others convenience.

    =COUNTIFS(Checkbox:Checkbox, 1, [Reason Code]:[Reason Code], "1122")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I am experiencing the same issue. Ive tried multiple countif formulas. Did anyone find a real answer to this?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Danielle Foley

    Can you describe your issue in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • I have tried all of the formulas and it is not working for me too.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Sarah Loewenthal

    I hope you're well and safe!

    What's not working? What are you trying to do?

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • fennerb
    fennerb ✭✭✭✭

    Hello!

    I'm trying to calculate the amount of contracts we have by counting the amount of checked checkboxes in our "Contract" column. I've been tinkering with it for a while, and have tried to utilize the functions and other pieces of information on this thread. I got as far as:

    =COUNTIF(ISBOOLEAN({Contract}:{Contract}), 1) +

    and keep getting "UNPARSEABLE," obviously. I'm a bit out of practice since I haven't made a report nor dashboard in a while, so I'm also a bit rusty.

    Any insight is greatly appreciated!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @fennerb

    It looks like you're using a cross-sheet reference, which means you only have to list it once, not twice like an in-sheet column reference.

    Try this:

    =COUNTIF({Contract}, 1)

  • fennerb
    fennerb ✭✭✭✭
    edited 06/15/21

    Hello!

    Thank you! That worked great.

  • Hi Andrée,

    I have a similar problem. I would like to count a specific colum to a different sheet.

    Example : colum 1 (i would like to count how many check box i received)

    Colum 2 (same)

    I am able to use the sheet summary with your suggestion (it's wonderfull) but, now I would like to report this data to another sheet be able to build my dash bord (chart)

    Do you have any suggestion please

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ly-Xu Ngo

    It sounds like you'll want to use {cross sheet references} instead of [In Sheet] column references. My example above uses this type of reference:

    =COUNTIF({Column with Boxes}, 1)

    Here's a webinar that goes through how to create these references in a Formula. See also this article: Cross-sheet formulas

    Cheers,

    Genevieve

  • Hi all -

    I have attempted many variations of the formulas listed above but none have worked. I am trying to count 2 columns - if something is a certain criteria, I want the checkboxes to counted. The latest formula I've tried is

    =COUNTIFS([{Range 5}:[{Range 5}], "1", [{Range 11}]:[{Range 12}], 1)

    The first column is a number that I want identified first and then the boxes to be counted if the criteria is met.


    Thank you all!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!