Counting cells with no blanks in a child column for multiple parent sections.
I have students(CHILD) categorized by University (PARENT) on one Smartsheet. I want to get the total number of students for that school in my sheet summary field. I was able to reach this with this formula. Here is an example of my Smartsheet:
=COUNTIFS(CHILDREN([Schools]1), NOT(ISBLANK(@cell)))
Answer: 2 which is CORRECT! Yeay!
MY PROBLEM:
I need to add up 2 of the schools totals together, CSUEB 1 + 2. I thought this formula below would work but it does not:
=COUNTIFS(CHILDREN([Schools]1), NOT(ISBLANK(@cell)), COUNTIFS(CHILDREN([End Date]4), NOT(ISBLANK(@cell))))
Answer: 2 but it should be 4
Can anyone help?
Answers

Perhaps something like this?
=COUNTIFS([Parent Name]1:[Parent Name]13, "CSUEB 1", [Data Present]1:[Data Present]13, "Data") + COUNTIFS([Parent Name]1:[Parent Name]13, "CSUEB 2", [Data Present]1:[Data Present]13, "Data")
I created two helper columns: one holds the name of the parent row of each child and the second checks to see if the child row is blank in the primary column.
Parent Name column formula: =PARENT(Schools@row)
Data Present column formula: =IF(ISBLANK(Schools@row), "Blank", "Data")

Thanks, Sam. Do I have to use helper columns? I am using the Sheet Summary feature in my Smartsheet and just want to know if there is a formula place in a new fields to then pull into a dashboard. Is there a formula to do this?

I figured it out! To get the sum of the two values from formulas, I needed to add "SUM" in the beginning and separate the two countif formulas with a comma, and then wrap them both in open and closed brackets.
=SUM(COUNTIFS(CHILDREN([Schools]1), NOT(ISBLANK(@cell)), (COUNTIFS(CHILDREN([End Date]4), NOT(ISBLANK(@cell)))))

Nice job!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!