Calculating amounts based on categories

[laurigardner]

I have been asked to deal with calculating a work order for furniture for invoicing. And my excel skills and google-fu have failed me.

We receive a parent ID in one column and then have child IDs in another column. For example we may receive a collection called Afghan Rugs that has a parent (a default design and size), that parent may have children (indicating different styles or sizes). The children are in one cell.

You can have a parent without children. You can never have children without a parent.

I need to calculate how many parents and how many children there are in each category. For example with rugs there are 2 parents, 6 children, with artwork there are 2 parents, 9 children.

We have premium and non-premium categories. For example we have 2 non-premium categories (rugs and artwork), which have a total of 4 parents and 8 children.

If armchairs go into non-premium, I can switch it to N, and the formula will update with 6 parents, and 13 children.

I can then calculate from here the cost of each.


Please find the example excel sheet attached.