How can I count Uniques in a group?

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)

Answer 1

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.

Answer 2

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: