# How to count elements on a structure

Options
✭✭

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!

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭
edited 04/08/22
Options

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.