Formulas with checkbox columns

Susan Pattison

I am trying to count the number of instances within a parent group that a checkbox is checked. I'd liek to count the number of checkboxes per parent group on my sheet, I have many parent groups. Below is one, it has 7 children.

Here is my setup... I should get 5. But I am 22, the total for the whole sheet, not just for the current parent

Suggestions, how I can limit this to just the children of THIS parent group?

susan

Kelly Moore

Hey @Susan Pattison

Try this

=COUNTIFS(CHILDREN([Backlog?]@row), 1)

If you want to make this a column formula which executes only in parent rows, here's one way

=IF(COUNT(CHILDREN([your primary column]@row))>0, COUNTIFS(CHILDREN([Backlog?]@row), 1))

cheers,

Kelly

Susan Pattison

This worked beautifully, THANK-you!

Susan

Susan Pattison

I have an additional question, using the same concept across sheets.

I would like the checked box in SHEET 1, to translate to a RED/GREEN in SHEET 2.

In both Sheets, I have the same parent group and within it the same children

SHEET 2 setup, at the RED/GREEN column, the assignment of values is incorrect

SHEET 1 Setup (the reference sheet/source for the checked box --> informs color of matching child in SHEET 2 of that parent group. Sheet 2 for "Total Bile Acids" and for "FGFR2 (Biopsy) Block" should be green, the others red.

Thank-you!

susan

Kelly Moore

Hey @Susan Pattison

I believe there's only the smallest of adjustments to your original formula. Within the MATCH function you have a '1' which would indicate your range is sorted. Sheet1 is your range and it is unsorted. Replace the '1' with a '0'.

In the event you would not have a match, rather than showing #NOMATCH in the column, you could consider adding the IFERROR to your formula.

=IFERROR(IF(INDEX({Referral Labs Manager List Range 3}, MATCH([Product Category]@row, {Referral Labs Manager List Range 2}, 0)) = 1, "Red", "Green"),"")

As a good practice for building cross-sheet reference formulas in the future, before clicking the Insert cross-sheet reference - rename the reference so that it is clear in the formula what the range refers to. My personal preference is to keep the sheet name in the reference but to replace the generic range number with the column name I used. This helps me understand my own formulas when I look back on a sheet.

Here's more info for your convenience

Kelly