CountIf for check boxes

2»

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Abe B.

    It sounds like you may be doing a cross-sheet formula, looking at a different sheet, is that correct?

    If so, your formula should look something like this:

    =COUNTIF({Column with Checkboxes}, @cell = 1)

    If you're still receiving an error, can you post the formula you're using and a screen capture of the sheet you're looking at (but please block out sensitive data!)

    Cheers,

    Genevieve

  • Cindi Meche
    Cindi Meche ✭✭✭

    Hi, I am having a similar issue using COUTIF to calculate a column with checkboxes. I am using:

    =COUNTIF({Linked FIle Range 1} @cell = Social Media)

    It returns #UNPARSABLE

    Using quotes around "Social Media" does not solve the issue. Does anyone have a suggestion?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Cindi Meche

    You will need the quotes around "Social Media", and you will also need a comma between the {range} and the criteria!

    Try this:

    =COUNTIF({Linked FIle Range 1}, @cell = "Social Media")

    You could also go right into the criteria, like so:

    =COUNTIF({Linked FIle Range 1}, "Social Media")


    Cheers,

    Genevieve

  • Cindi Meche
    Cindi Meche ✭✭✭

    Thanks, Genevieve!

    I went with this version: =COUNTIF({Linked FIle Range 1}, "Social Media")

    I'm one step closer, but now it tallies 0 when I know I have 4. I double-checked to make sure I had the correct column of data selected. Any idea why it's not pulling any data?

    Do you have any other suggestions?


  • Cindi Meche
    Cindi Meche ✭✭✭

    OK... so dropdowns are not checkboxes. Ha ha ha!

    I got it to work using =COUNTIF({Linked File Data Range 1}, CONTAINS("Social Media", @cell))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Cindi Meche

    Thank you for the screen captures, that's very helpful!

    I can see that it's not just a dropdown list but a multi-select dropdown which will change the formula you'll want to use. Instead of CONTAINS, try using HAS. HAS is specifically made to see if a multi-select cell has a specific value, amongst other values.

    Try this:

    =COUNTIF({Linked File Data Range 1}, HAS(@cell, "Social Media"))


    We can even take it a step further since you have "Social Media" written in your formula sheet:

    =COUNTIF({Linked File Data Range 1}, HAS(@cell, Criteria@row))

    Then you can drag-fill this formula down without needing to change what's "in quotes" each row.

    Let me know if this works for you!

    Cheers,

    Genevieve

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

    @Sashank Gutala

    Excellent!

    Glad to hear it was helpful!

    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.

  • Yanneth
    Yanneth ✭✭✭

    I am having problems counting the total of checkboxes marked in the sheet.

    I want to capture the total of column FY-21 with checkboxes marked. Any help is appreciated.

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

    Hi @Yanneth

    Adding the answer here as well.

    I hope you're well and safe!

    Try something like this.

    • If it's placed in another column, than the range.
    =COUNTIFS([CHECKBOX FY-21]:[CHECKBOX FY-21], 1)
    
    • If it's placed in the same column as the range.
    =COUNTIFS([CHECKBOX FY-21]:[CHECKBOX FY-21], 1)+""
    

    Did that work/help?

    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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.

  • HI,


    I would like if you can help me with this formula please.

    =COUNTIF({Cadre21_registre des inscriptions}, CONTAINS(@cell, "1- Autobus"), CONTAINS(@cell, "101A Communication entraîneur") = 1)

    I would like to count 2 critera : 1- Autobus (Colum "Unité Sectorielle") + check box (colum "101A Communication entraineur") this sheet is on "Cadre21 registre des inscriptions"

    Thank you :)

  • No matter what I try, I am still getting #UNPARSABLE -- Also, I don't want to count the whole column. I ony wants to count rows 3-13 for this specific formula. It should be returning 0 since none are checked, but I'm just getting the error. Any help?



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ly-Xu Ngo

    If you're looking for 2 criteria, you'll want to use a plural COUNTIFS with an S at the end.

    You also don't need to use CONTAINS if you're looking for one specific value (contains is if you're looking for partial text within a cell, not a full match).

    Try this:

    =COUNTIFS({Cadre21_registre des inscriptions}, "1- Autobus", {101A Communication entraîneur}, 1)

    Note that you will need to create a second {cross sheet reference} to find the column with your checkboxes.

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Dieter21

    It looks like the column name you've typed [in these] is incorrect. You've typed [Checkbox Complete] but the column to the left is only titled "Complete"

    If you only want to count a specific selection of rows, you can add these row numbers after each column reference, like so:

    Complete3:Complete13

    Try this:

    =COUNTIFS(Complete3:Complete13, 1)

    See: Create a Cell or Column Reference in a Formula

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!