Hierarchical or recursive counting in excel (preferably within a pivot table)?

Question

I'm trying to find a way to aggregate data in a hierarchical data set, preferably within a pivot table but other methods might be OK as well. Consider a data set (greatly simplified for the example) that looks like the one below. From this data, I'm trying to build a set of functions that will answer questions like:

"How much total inventory do I have for Fruit?"

"How many different kinds of Food do I sell?"

Item     Category
=======  ========
Apples   Fruit
Bacon    Meat
Chicken  Meat
Corn     Veg
Food     
Fruit    Food
Grapes   Fruit
Meat     Food
Squash   Veg
Steak    Meat
Veg      Food

Each Item has (among lots of other information) a Category, which we can really think of as a "parent". But also note that within the data set, all the "parents" also have their own parent categories. In this data set one sample 'branch' of the hierarchy would be Food->Meat->Chicken.

To answer the question like "How many different kinds of Fruit do I sell" it's not hard, because this is first level category. I can just use the COUNTIF function and say "How many Items belong to Category "Fruit"?" -- and I get a table that looks like this:

Item    Category    COUNTIF(categories,me)
Apples  Fruit       0
Bacon   Meat        0
Chicken Meat        0
Corn    Veg         0
Food    Food        3
Fruit   Food        2
Grapes  Fruit       0
Meat    Food        3
Squash  Veg         0
Steak   Meat        0
Veg     Food        2

Easy - for the first row you just see how many times "Apples" appears as someone else's Category. (Since it's zero, I know that Apples are not a parent... this should help, but I'm not sure how...) Now row five, "Fruit", appears as someone else's Category two times - since the number is NOT zero, I know it's a Category instead of just an Item. All well and good for the first level math, but...

This leads me to the part that I haven't been able to solve... How do I figure out how many TOTAL kinds of "Food" I have? And given that my actual data has many more levels of hierarchy, I need to walk up and down the tree to figure out how many total chidren are in each one. The first level COUNTIF function tells me that there are three subcategories of Food (Fruit, Veg, & Meat) -- but what I really want is to somehow have it recursively determine that Fruit, Veg, and Meat might also be Categories, and sum up the corresponding numbers for those children. In excel terms, what I really want is to be able to build another column that recursively/iteratively counts the TOTAL number of items in that whole subtree... in this case, there are seven unique Items that belong to Food: 3 meats, 2 veg, and 2 fruit.

Some complicating factors:

• There's no explicit identifier in the data to tell us whether that particular item is also a category, or if it's a bottom-level item.

• Each item only knows what it's category/parent is - there is no explicit data to tell whether it has children or not. Said another way: all Items belong to a Category, but only some Items are also Categories.

• In the actual data the parent relationship can get as much as 10 levels deep, BUT there are no guarantees that the depth of each branch in the hierarchy is consistent: some items might be 3 levels deep while the next one might be 8.

• The root or ultimate parent doesn't come with a category, but this is a one-off case that I can easily handle manually.

• I'm fully aware that this would be a trivial exercise in any 'real' programming language (Perl, Python, etc)... but ultimately I have to hand this off to someone who does not have programming experience, so I'm trying very very hard to make this fit into a "standard" Excel workbook.

Answer 1

At first, I fully agree with @Raystafarian, Excel isn't the right tool for that.

However, if you really want to do it here, here is a solution with a few helper columns:

• level: level of actual item in hierarchy (root items has level 1, children's level is increased)
  =IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1)
• level code: running code for each item, unique WITHIN LEVELS
  =CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1)
• long code: concatenated code of parent & item
  =IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]]
• has child: boolean telling whether item has child
  =COUNTIF([Category],[@Item])>0

With this model a category contains all items and sub-categories of which code starts with the same sequence as parents code (e.g. if fruit's code is aa, then all (grand...)children of it have a code starting with aa)

Answers for your questions:

"How much total inventory do I have for Fruit?"

=COUNTIFS(Table1[long code],VLOOKUP(I3,Table1,5,FALSE)&"*",Table1[has child],FALSE)
just according to the model, all items with the same starting sequence. I coun't here only items not categories (you sell two type of fruits apples and grapes, you don't have a product called fruit to sell). If you want to count also categories, then just exclude second part of the formula.

"How many different kinds of Food do I sell?"

=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
Pretty similar with SUMIF

Warning

This solution has two limitations:

• number of characters: it currently starts from a, which has a code of 97, and last supported character in CHAR function is 255, so having more than 158 different categories in any level will give you an error (you can expand it a bit using character with smaller code for first one)
• as your database increases it probably will have decreased performance (complex calculations), you may want to set calculation method to "automatic except for data tables" and calculate it manually just when you need it.

Answer 2

I think Máté has a good answer. The way I would do it would be with lists:

(All array formulas, so Ctrl+Shft+Enter)

So, the formulas (drag down)

D2 =INDEX($A$2:$A$12,MATCH(0,IF(ISBLANK($B$2:$B$12),COUNTIF($D$1:$D1,$A$2:$A$12),""),0))    
E2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$D$2,COUNTIF($E$1:$E1,$A$2:$A$12),""),0))
F2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$2,COUNTIF($F$1:$F1,$A$2:$A$12),""),0))
G2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$3,COUNTIF($G$1:$G1,$A$2:$A$12),""),0))
H2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$4,COUNTIF($H$1:$H1,$A$2:$A$12),""),0))

Now, depending on how you decide to sort those, you can definitely create a hierarchy or use a pivot table.

I'd probably give A and B columns names so you can work with named ranges.