Formula to Count Check Boxes in a Check Box Column

Trying to count in coumns how many boaxes are checked per column - Keep getting Boolean Expected. There are the formulas I have tried:

=COUNTIFS([Mike M]5:[Mike M]135, 1)

=COUNTIF([Mike M]5:[Mike M]135, 1)

Is it possible to created this type of formula?

Find more posts tagged with

Accepted answers

Andrée Starå

Hi,

It's because you're referencing the same column, and the value expected is 0 or 1 for the checkbox.

If you add +"" to the end of the formula, it will show the result.

=COUNTIF([Mike M]5:[Mike M]135, 1)+""

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Genevieve P.

Hi @JSabillon

Are you looking in a current sheet or across sheets?

A current sheet would use column references in square brackets, with the name listed twice. You list the name twice because you could do a range from specific row numbers, such as row 5 - 10:

[Column Name]5:[Column Name]10

or you could simply want the entire column to be referenced (so you take out the numbers):

[Column Name]:[Column Name]

However in your formula I see you have {Ranges} listed. I suspect this means you're looking in a different sheet. In this case, you only need to list the range once, as you would have selected the number of cells in that column or range:

{Column Reference}

See:Create a Cell or Column Reference in a Formula

Then, when you're looking for a checked box, you simply need to search for 1 (without quotes).

Try this:

=COUNTIFS({Range 5}, 1, {Range 11}, 1)

This will only count rows where your column Range 5 has a checked box and in the same row your column Range 11 has a checked box. Is this what you were looking to do?

If not, it would be helpful to see a screen capture of your source sheet, but please block out sensitive data.

Cheers,

Genevieve

JSabillon

@Genevieve P. Thank you for help! Some context:

I am pulling this data from one Smartsheet into a metrics sheet for a dashboard.

The first column I want to use is the one I want to identify as either a 1, 2, 3, 4, 5, or 6 (these are labels I created to count the # of people in specific groups on the sheet).

The second column I want to us is the one that has the checkboxes. I want to count only the people in that particular group from the first set of criteria that also have checked boxes.

Both of these columns are coming from a separate Smartsheet and being pulled into a metrics sheet. My goal is to count how many people have completed a check-in.

Thank you!

Genevieve P.

Hi @JSabillon

Thanks for clarifying that this is a cross-sheet formula! In this case, did you try the one I suggested?

=COUNTIFS({Range 5}, 1, {Range 11}, 1)

Where {Range 5} is the column that has your numbers (1, 2, 3, 4, 5, or 6) and {Range 11} is the checkbox column.

JSabillon

That worked!! Thank you, Genevieve. For anyone else using this comment as their answer, make sure to put the " " around the first 1/criteria so that it knows to find that criteria on the sheet!

=COUNTIFS({Range 5}, "1", {Range 11}, 1)

meirney

(Q1) is there a way to create a condition / automation that says if:

children = checkbox

parent = counts the checkbox of the children

(Q2) i manually added this formula in the parent cells =COUNTIF(CHILDREN(), 1) + ""

it works for the grey rows.

in the black row however, i need a formula to count all the subtotal of the subtotals below level 0

Genevieve P.

Hi @meirney

The top black row is unable to SUM the values in the grey rows because they're now seen as text, due to the + "" at the end of your formula.

What about counting all the checkboxes in the Level 2 rows?

Try:

=COUNTIF(DESCENDANTS(), 1) + ""

See: DESCENDANTS Function

Cheers,

Genevieve

meirney

thanks, @Genevieve P.

this works:

=COUNTIF(DESCENDANTS(), 1) + ""

do you know if there's a way to use this as a column formula (while retaining the checkboxes in the level 2 rows)?

Genevieve P.

Hi @meirney

If the check boxes are manually being input, then no, there's no current way to make this a column formula. Column formulas apply to every cell in the column so you wouldn't be able to check the boxes manually as well.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.