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?

S_H

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