Looking for a formula for checked boxes xx/xx checked

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!

Find more posts tagged with

Formulas

Comments

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.

Screenshot 2019-10-08 at 10.26.51 PM.png

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

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…