Using COUNT and CHILDREN functions to return non-blank rows under parent row of a different sheet

Katy Richardson

Hi Smartsheet Community,

I need a formula which will allow me to look up the number of non-blank rows under a parent row from another sheet and return the value.

As an example I've attached a screenshot of my reference sheet where I am trying to count the non-blank rows under the parent row "Over 50%", some of the information has been blocked out for confidentiality. I will also need to repeat this for the other categories on the sheet such as "Confirmed" etc.

I didn't want to define the range too specifically (for example just use the COUNT function and only reference the exact rows under the parent row) as I need the owner of the reference sheet to be able to insert and delete rows freely under each category without it affecting the formula or a row accidentally being added outside of the reference range.

I've tried using a combination of COUNT / COUNTIF with PARENT / CHILDREN but don't seem to be getting the result I need.

Please help?

Thanks!

Katy

Ref Sheet.png

Katy Richardson

Guys thanks so much for all your help and great ideas!

I actually came to a slightly different solution in the end, I added a hidden column in the source sheet and used the COUNT function to total the non-blank rows under each parent, I only needed to use the top cell next to each category for the formula, then I simply linked the two cells together via cell linking rather than using a cross sheet formula.

The formula in the source sheet now seems to successfully count the number of non-blank rows under each category, and the total seems to adjust accordingly when new rows are added and removed which was my main concern.

I've attached some screenshots for reference in case anybody is interested! :)

Thanks again everyone!

Best wishes,

Katy

Screenshot 2020-02-07 at 12.30.07.png

Screenshot 2020-02-07 at 12.30.36.png

Genevieve P.

Hi Katy,

My preferred way to do this would be to add in a helper column in the source sheet to indicate what the name of the parent row is for each child row, blank or not. Then you can do a cross-sheet COUNTIFS formula to count all rows with the specific parent name in your helper column and if the Team name is blank or not.

This would be an example formula to return the parent's name for the child row. It looks to see if the Child count is 1 or more, and if it is, returns the words "Parent Row". If there are no children, it will return the Parent's title from the Team column:

=IF(COUNT(CHILDREN(Team@row)) >= 1, "Parent Row", PARENT(Team@row))

Then you could use this COUNTIFS formula in your other sheet:

=COUNTIFS({Parent Name in Source Sheet}, "Confirmed", {Team Column in Source Sheet}, NOT(ISBLANK(@cell)))

Just change out the "Confirmed" for your other parent names to count the different child tasks. Here are some Help Center articles I used to create these formulas: COUNTIFS function / @cell function / cross sheet formulas

Let me know if this works for you!

Cheers,

Genevieve

Katy Richardson

Hi Genevieve,

Thanks for your suggestion! I will try that, however this still doesn't solve the issue if the owner of the source sheet were to insert a new row under one of the categories? The user needs to be able to do this without having to fiddle around with updating formulas and my worry is that the user can still insert what is essentially a blank row and then whatever labelling / formulas I've set up in the source sheet will no longer be capturing all the information.

Do you have any workarounds for this?

Thanks,

Katy

Eric M Oliveira

Hello,

Happy to help investigate into if there is a way to reference and count the blank rows of children rows using a cross sheet formula. Currently we do not have a way to achieve the desired result directly, the following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula. This is further outlined here: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

However, you may be able to achieve your desired result by creating a checkbox column on the source sheet that checks a box if the row is a child row. Then you can utilize a COUNTIFS to count the values that are check and are blank. The formula could look like this.

=COUNTIFS({Checkbox Column ref}, 1, {Value Column}, ISBLANK(@cell))

The formula to create the checkbox could look like this. This would also address the issue of a user adding a new row because it would automatically be taken into consideration and alter the resulting value accordingly:

=IF(PARENT([Primary Column]7) > 1, 1, 0)

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Genevieve P.

Hi Katy,

To add on to Eric's advice, formulas will auto-populate when new rows are inserted.

You’ll see automatic formatting occur when you type in a newly inserted or blank row that is:

• Directly between two others that have the same formatting applied.
• At the topmost row of the sheet if it's above two others that have the same formatting applied. (This includes rows inserted from a form.)
• At the bottommost row of the sheet if it's below two rows that have the same formatting applied. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formatting.

You can find this information in our Help Center article, click here. If you don't want the user fiddling with the formula, you could hide the row so that it's less susceptible to edits.

Hope that helps!

Genevieve

Paul Newcome

In addition to @Genevieve P's response regarding auto-fill...

If someone inserts a row but does not add any data to any column, then the formula will not autofill until data is added somewhere. Then it will follow the rules as outlined by Genevieve.

Genevieve P.

Great point, Paul! I've been caught by that a few times haha.

Katy Richardson

Guys thanks so much for all your help and great ideas!

I actually came to a slightly different solution in the end, I added a hidden column in the source sheet and used the COUNT function to total the non-blank rows under each parent, I only needed to use the top cell next to each category for the formula, then I simply linked the two cells together via cell linking rather than using a cross sheet formula.

The formula in the source sheet now seems to successfully count the number of non-blank rows under each category, and the total seems to adjust accordingly when new rows are added and removed which was my main concern.

I've attached some screenshots for reference in case anybody is interested! :)

Thanks again everyone!

Best wishes,

Katy

Screenshot 2020-02-07 at 12.30.07.png

Screenshot 2020-02-07 at 12.30.36.png

Paul Newcome

Happy to help! 👍️

Glad you were able to find a working solution! Don't forget to mark the most appropriate response(s) (even your own) as "helpful" to flag them as the "Best Answer". This way others searching for a solution to a similar problem can know that one may be found here.