Looking for a formula for checked boxes xx/xx checked

edited 12/09/19 in Formulas and Functions

I am working on a tracking type of sheet. I have 4 columns that i have inserted check marks for:






The check marks are great, but i would like to take this one step further and have a tracker up at the top that as an example says xx/1072 to signify how many boxes are checked out of how many total.  Any help  would be greatly appreciated and thanks in advance!



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To count how many are checked...


    =COUNTIFS(D2:D301, 1)


    To count the total...


    =COUNTIFS(D2:D301, OR(@cell = 1, @cell = 0))


    Now we just add the / in the middle and you get...


    =COUNTIFS(D2:D301, 1) + "/" + COUNTIFS(D2:D301, OR(@cell = 1, @cell = 0))

  • Thank you for your reply, i have never used the count ifs before so that is handy to know and learn more about. I am running into an issue in the first step though. I tried to do everything at once and that was a mess lol, so i broke it down by step. When i start with just this:

    =COUNTIFS(D2:D301, 1)

    i do get a count, but that count is 0. When i try to change that 1 to a 0 like so:

    =COUNTIFS(D2:D301, 0)

    i also get 0. Any idea's? I am assuming the 1/0 are a true and false type of statement, but i find it weird they are both returning as 0. I double checked i had the right cells selected, just in case.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    And the column type is definitely a checkbox type column? It's odd that it won't register the 1 vs 0 (which yes is a true = 1 vs false = 0 type of value).

  • tbiztec
    edited 10/08/19

    I added a small screenshot to show the formula that i have, the cells included (which i lowered just to test)  and to show the check marks. I also have tried the 1 and 0 for true and false in the same spot as the formula to see if i got anything different. 

    Granted i am doing this in google sheets, but i have seen that 95% of google sheets is the same as excel and to be honest i am not sure what smart sheets is, but i assume its close as well. I came here because when i was searching for a solution to the problem i saw the forum and figured it was filled with knowledge that i could gain. I am sure i am just missing something simple, which is usually the case. Appreciate the help.

    Screenshot 2019-10-08 at 10.26.51 PM.png

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. I have not used Google Sheets before. The best advice I can give you is that you need to confirm the proper syntax and then find exactly what value needs to be used for "true". 


    I imagine the syntax is probably correct though, so I would start by searching for what value Google Sheets uses either for a checked box or a "true" (It could be different. I don't know.)

  • much appreciated! i will start there. i do know google sheets is weird like that. even when comparing to excel things are just small differences, so that makes a lot of sense. ill see what i can dig up and again thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    They all have their little differences here and there. Glad I was able to help get you started in the right direction. yes

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


    Did you get it working or do you still need help?

    Have a fantastic week!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD




    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!