Formula to count child rows that have not expired

Jessica Chaperon

Hi all,

I have a sheet that contains parent and child rows. Each child row has an expiration date and I want to count the number of child rows that have not expired based on the expiration date in that row. Can you assist? Thanks!

David Tutwiler

You should be able to use this formula in the parent row. You might need to give it a reference if you're putting the formula on a different column than the dates. Something like this:

=COUNTIF(CHILDREN(Date@row), <TODAY())

Jessica Chaperon

Thanks David. Yes, I have a column titled active engagements that will count the number of engagements (which are listed as child rows) that have NOT expired so it sounds like I will need a reference. Can you help with with the (date@row) part?

David Tutwiler

I may be confused about exactly what you're doing. The formula that I used essentially makes a formula on the Parent row on say the Formula column. But it uses the CHILDREN function to look at all of the child rows on the Date column to check the date. So you'll need to replace Date@row to whatever the column name that contains your date.

If you're looking to check a few things, like the fact that the date is before today AND that the number is greater than 0, then you'll need COUNTIFS. Or if you're looking to add up a different column based on the results in one column, then you're looking for SUMIF. It would look like:

=SUMIF([The column you would use to add the numbers up], [the condition column like Date], < TODAY())

Jessica Chaperon

I'm including a snapshot of what I'm trying to do. I appreciate your help with this.

In the 'Total Active Engagements' column, I'd like the formula to count the child rows that have not expired, based on the date in that column. So if the date in the Engagement Expiration column is AFTER today, then it'll count that child row. In my sheet, the child rows represent engagements that I want to count.