How to count elements on a structure

Aider

I have a “Request” sheet that references a bill of material (BOM) structure, similar to the table below

I am trying to determine that if in my “Request” sheet I get a request for Part C, then I should be able to count from my BOM sheet that I only need 3 units of Part E, but if I have an order for Part B, then I would need 4 units of part E.

I have tried to use Ancestors, rows counts, etc, but so far I have not been able to come with a logic that allows me to do this.

Your help will be greatly appreciated!

Thanks in advance.

Jon Mark H

Hey!

So it looks like what you need is Descendants

=COUNTIF(DESCENDANTS([Primary Column]@row), "Part E")

How you would structure the use of it would depend on how you're designing and wanting to utilize your "Request Sheet"

Does this solve your problem? I'd love to help you more if this doesn't get you all the way there!

Let me know!

-Jon Mark

Jon Mark H

Hey!

So it looks like what you need is Descendants

=COUNTIF(DESCENDANTS([Primary Column]@row), "Part E")

How you would structure the use of it would depend on how you're designing and wanting to utilize your "Request Sheet"

Does this solve your problem? I'd love to help you more if this doesn't get you all the way there!

Let me know!

-Jon Mark

Aider

Yes, that is the Formula I was looking for. On the formula refernce pages for "Parent" or "Children". "Descendants" is not listed as related function. It would be great to add it to those lists.

@Jon Mark H, thanks for your help and promp reply!!

Jon Mark H

Awesome! Glad I could help!