Finding how many child records for a given parent record

Hi everyone,

In this table, I have 2 projects, CAPAs and Extensions. Extensions are child records of CAPAs.
I need a formula in column Q that counts how many Extensions does each CAPA have, based on the ID column A. Column A has the parent IDs and child IDs. So in column Q, only CAPAs will have a corresponding number that it can be 0, 1, 2, etc, depending on how many child records belong to the CAPA.

I attached the file as well.

I hope I clearly explained what I need.

Thank you guys

CA.png

Hi,

How do you relate an extension back to its parent CAPA? I can't see any connection there based on what you posted, and the ParentID column in the file is extremely confusing.

xlnitwit is correct, it's hard to tell how you have the relationships stacked. If it's just sequential, then the following should work in Q2, filled down:

=IFERROR(IF($P2="CAPA",MATCH($P2,$P3:$P$452,0)-1,""),"In Progress")

You could use a formula like this
=IF(P2="CAPA",COUNTIF(L:L,A2),"")
though many of your parent IDs do not seem to exist in the file.

It looks like some of the CAPAs have parents, as well. Should those also be counted as children? If yes, try the following in H2 and fill down:

=IF(G2="CAPA",COUNTIF($D$2:$D$452,$A2),"")

This will count the occasions on which the CAPA's ID is listed in the "Parent ID" column.

EDIT: Ha! I was the slowest to the COUNTIF party, so I'll include this addendum - to count only the "Extension" children, you can use:

=IF(G2="CAPA",COUNTIFS($D$2:$D$452,$A2,$G$2:$G$452,"Extension"),"")

Guys, thanks so much. I thought it was a lot more complicated.

I just added a similar parent ID number for 3 extensions.

CA solved.png