# Looking for a formula for checked boxes xx/xx checked

edited 12/09/19

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

D2:D301

I2:I301

N2:301

S2:S177

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!

Tags:

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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).

• 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.

• ✭✭✭✭✭✭

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!

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Hi,

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

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!