Question: I've got a list of data which has a hierarchy of sorts. There's the primary group which then falls into secondary group which then has a tertiary group of unique data. I'm trying to figure out how to represent the number of unique secondary groups in a primary group.

E.g. Group A has a list of subgroups `A-1,A-1,A-2,A-2,A-2,A-3` and Group B has a list of subgroups `B-1,B-2,B-2.` In here I want to show in a chart how many unique subgroups there are in a group and fraction of each, i.e. Group A has 3 subgroups; `2 A-1, 3 A-2, 1 A-3,` and Group B has 2 subgroups; `1 B-1` and `2 B-2.`

The increased hierarchical orders throw me for a loop. Any ideas?

Edit: I've included an example of how the data looks roughly (just several magnitudes more data) ![Example table](https://i.stack.imgur.com/3xuLl.jpg)

Answer: 
Use this one array formula:

```
=SUM(IF($A$2:$A$23=E2,1/COUNTIFS($A$2:$A$23,E2,$B$2:$B$23,$B$2:$B$23)))

```

being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put `{}` around the formula.

[![enter image description here](https://i.stack.imgur.com/uni60.png)](https://i.stack.imgur.com/uni60.png)
You can do this with a couple of helper columns.

First in Column D put this formula in D2 and drag it down:

```
=A2&B2

```

Then in Column E enter this formula as an array (paste and then press CTRL+SHIFT+ENTER) and drag it down until you get a '0':

```
=INDEX($D$3:$D$100, MATCH(0, COUNTIF($E$1:E1, $D$3:$D$100), 0))

```

Then in Column F you can start your table by entering the Alpha variables (A,B,C).

In Column G enter this formula in G2 and drag down:

```
=COUNTIF($E:$E,$F2&"*")

```

You should end up with something like this:
[![enter image description here](https://i.stack.imgur.com/IPYnk.png)](https://i.stack.imgur.com/IPYnk.png)