Sum Hierarchy Data Using Flattened Hierarchy

[neb255]

Excel 365 for Enterprise -- I have access to LET, and Xlookup etc.

Hi,
I am trying to create a rollup of data using an unsorted collapsed hierarchy, by which I mean that the parent child relationships do not follow each other in the spreadsheet so I cannot just take the sum of rows above or below the current row.

The example Hierarchy has 6 people in it which looks like this

1. Arnold
   1. Mike
   2. Anthony
      1. Bill
         1. John
         2. Alex

The underlying data has the the "Rep" listed as anyone who has a value directly associated with their name, and their direct "Manager" and looks like the below.

Underlying Data

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Manager	Rep	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	FY
3	Bill	John	138	265	178	240	824	230	126	469	349	641	463	838	4,761
4	Bill	Alex	158	144	651	846	282	366	177	101	764	745	114	788	5,136
5	Anthony	Bill	812	728	107	899	556	182	203	788	721	740	768	467	6,971
6	Bill	John	92	177	119	160	549	153	84	313	233	427	309	559	3,174
7	Arnold	Mike	105	96	434	564	188	244	118	67	509	497	76	525	3,424
8		Total	1,305	1,410	1,489	2,709	2,399	1,175	708	1,738	2,576	3,050	1,730	3,177	23,466

What I need to do is create a rollup by rep, rep to manager, rep to manager to manager in a second table. So in this case, anything which sits with John or Alex would roll up to Bill, and Bill would show his own records added to the rollup from John and Alex. Then since Anthony does not have anything directly in his name would get the rollup from Bill which already includes the rollup from John and Alex. Likewise Arnold would receive the rollup from Mike who has direct numbers and Anthony who has the rollup of Bill, John, and Alex. Which should look like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
	Manager	Rep	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	FY
11	Bill	Alex	158	144	651	846	282	366	177	101	764	745	114	788	5,136
12	Arnold	Anthony	1,200	1,314	1,055	2,145	2,211	931	590	1,671	2,067	2,553	1,654	2,652	20,042
13		Arnold	1,305	1,410	1,489	2,709	2,399	1,175	708	1,738	2,576	3,050	1,730	3,177	23,466
14	Anthony	Bill	1,200	1,314	1,055	2,145	2,211	931	590	1,671	2,067	2,553	1,654	2,652	20,042
15	Bill	John	230	442	297	400	1,373	383	210	782	582	1,068	772	1,397	7,935
16	John	Mike	105	96	434	564	188	244	118	67	509	497	76	525	3,424

The formula I am currently using (in cell C11) is =SUMIFS(C$3:C$7,$A$3:$A$7,$B11)+SUMIFS(C$3:C$7,$B$3:$B$7,$B11) which takes the sum of the top table in column C if the Rep name is the same as the rep name in table two column B, then adds the sum of the top table if the Rep also appears as a Manager

The problem I am having is that this formula does not return the correct response for Arnold or Anthony since in the top table Arnold would only return Mike and Anthony would only return Bill's so their result would look like this:

Manager	Rep	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	FY
Arnold	Anthony	812	728	107	899	556	182	203	788	721	740	768	467	6,971
	Arnold	105	96	434	564	188	244	118	67	509	497	76	525	3,424

I have also tried adding an additional sumif to the 2nd table to add the value if the "Rep" also appears as a manager in it +SUMIFS(C$11:C$16,$A$11:$A$16,B12) but this creates a circular reference which double counts for any managers who appear in the top table.

The format of the 2 tables here are fixed and cannot be changed, and the actual hierarchy can range anywhere from 2 to 6 layers deep and is a few hundred individuals. We are also using unique identifiers instead of names so there is no risk of duplication from that respect. The reason I am using SUMIFS instead of SUMIF is that in the full dataset we are also cutting the data by a few other categories.

What I need is a way to recognize in the second table that Anthony's numbers need to roll up not just Bill but also Bill's reports John and Alex. And that Arnold should roll up from Anthony and Mike. Without double counting the amounts which were already rolled up to the "middle managers." In theory I could create a separate table for every single tier of management but that would become impossible to maintain over time. I am open to Power Query or VBA responses as well, however calculation speed is an issue since there are approximately 75k cells which will have the formula in it so array's are a no-no.

 

[neb255]

I should also note, that since not all "Reps" from the first table will appear in the second table, the Manager rollup needs to be sourced from the first table otherwise it could end up with a reduced value.

Also in second table Mike should report to Arnold not John.

16

Arnold

Mike

105

96

434

564

188

244

118

67

509

497

76

525

3,424