Count Children formula

Deanna Vandermeer

I have this formula that is working properly except I need to adjust it. Basically what this formula is doing is counting the children rows if a specific date column has nothing (is blank). However, what I need to also adjust for is to have my formula return a "zero (0)" if the units have expired (captured in a helper symbol column with a formula that raises the flag if the units are expired). What else do I need to add to make this formula work properly? I am stumped, any help would be greatly appreciated, thank you.

My formula currently is this ...

=COUNTIFS(CHILDREN([Stipend Requested]@row, NOT(ISDATE(@cell)))

Paul Newcome

I think I understand. Give this a try...

=COUNTIFS(CHILDREN([Stipend Requested]@row, NOT(ISDATE(@cell)), CHILDREN([Stipend Requested date column]@row), @cell <> 1)

Paul Newcome

Do you want to count blanks and flagged rows? Are you wanting to count blanks unless there is a flagged row in which case return a zero?

Are you able to provide a screenshot of a mock-up that shows what you are wanting to accomplish with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

Deanna Vandermeer

I am wanting to count blanks if there is not a flag in the parent row (means the course is no longer eligible). The sheet is rather large with quite a bit of data on it but the basic information is the parent row contains a start date column and an end date column (along with details about the course itself), then another date column with a formula to automatically calculate the expiration date for units associated with that course. Then from there I have a flag column with a formula that raises the flag once the expiration date is reached. This basically means that all the child rows (regardless of whether they applied for their stipend payment or not) are no longer eligible to apply and therefore I need to return a value of zero which is why I need to add to my existing count children formula to have it look at the flag and no longer count any blanks it sees in the Stipend Requested date column for that row. Not sure this makes sense, I can work on grabbing some screenshots if this is needed, I just need some time to work on that. Let me know if you still need that. Thanks.

Paul Newcome

I think I understand. Give this a try...

=COUNTIFS(CHILDREN([Stipend Requested]@row, NOT(ISDATE(@cell)), CHILDREN([Stipend Requested date column]@row), @cell <> 1)