Formula to Count Check Boxes in a Check Box Column

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.