Function for Counting Children of Parent Rows

Options
✭✭

Hi - I have been wrestling with this function and need some help.

Here is the data:

In my sheet summary, I am trying to enter a function to count the children who answer yes. I have tried it a bunch of ways, and it will count if I only ask it to count children, or if I only ask it to count the yes answers, but I cannot get them together.

=COUNTIF(CHILDREN([Month/Year - MRN]1, [Documented Flush q12?]:[Documented Flush q12?],"Y"))

Thoughts? Thanks!

Best Answers

• ✭✭✭✭✭✭
Answer ✓
Options

Hey @EB_RN

To use the Children function, here's the formula

=COUNTIFS(CHILDREN([Documented Flush q12?]1), "y")

From the snippet, it appears only children have Y/N responses and I can only see one parent, which made me wonder if the Children criteria was necessary

=COUNTIFS([Documented Flush q12?]:[Documented Flush q12?], "y")

cheers

Kelly

• ✭✭✭✭✭✭
Answer ✓
Options

Hey @EB_RN

As an alternative, you could also bring in your date column to help you filter by month. In my sheets I try very hard not to force myself to manually choose a specific row- I prefer formulas to choose data for me.

=COUNTIFS([Documented Flush q12?]:[Documented Flush q12?], "y", Date:Date, MONTH(@cell)=12)

I hope you have joyful holidays as well

Kelly

Answers

• ✭✭✭✭✭✭
Answer ✓
Options

Hey @EB_RN

To use the Children function, here's the formula

=COUNTIFS(CHILDREN([Documented Flush q12?]1), "y")

From the snippet, it appears only children have Y/N responses and I can only see one parent, which made me wonder if the Children criteria was necessary

=COUNTIFS([Documented Flush q12?]:[Documented Flush q12?], "y")

cheers

Kelly

• ✭✭
Options

@Kelly Moore It is a monthly audit, which is why I want the parent row for future months. But this worked perfectly, and I tried it out for the next month and it worked for the new group of data. thank you so much!! happy holidays!

• ✭✭✭✭✭✭
Answer ✓
Options

Hey @EB_RN

As an alternative, you could also bring in your date column to help you filter by month. In my sheets I try very hard not to force myself to manually choose a specific row- I prefer formulas to choose data for me.

=COUNTIFS([Documented Flush q12?]:[Documented Flush q12?], "y", Date:Date, MONTH(@cell)=12)

I hope you have joyful holidays as well

Kelly

• ✭✭
Options

oh my gosh that one is even better - you are so right, that is awesome. thank you again for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!