Counting cells with no blanks in a child column for multiple parent sections.

Charlene Pons

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?

Sam Harwart

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")

Charlene Pons

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?

Charlene Pons

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

Sam Harwart

Nice job!