Looking for a formula for checked boxes xx/xx checked

tbiztec

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!

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

tbiztec

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

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

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.

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

tbiztec

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

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

Andrée Starå

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