Using COUNT and CHILDREN functions to return nonblank rows under parent row of a different sheet
Hi Smartsheet Community,
I need a formula which will allow me to look up the number of nonblank 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 nonblank 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
Best Answer

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 nonblank 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 nonblank 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
Answers

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 crosssheet 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

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

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 CROSSSHEET FORMULA error in the cell containing the formula. This is further outlined here: https://help.smartsheet.com/learningtrack/smartsheetadvanced/crosssheetformulas
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

Hi Katy,
To add on to Eric's advice, formulas will autopopulate 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

In addition to @Genevieve P's response regarding autofill...
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.

Great point, Paul! I've been caught by that a few times haha.
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

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 nonblank 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 nonblank 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

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!