Sum list to get hierarchy

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Sum list to get hierarchy

Post by gailb »

I've created a formula for column H:I, but wondering if there is a smarter way to do this. Also, I've added a formula in column A to help establish the length of the hierarchy. Any thoughts?
You do not have the required permissions to view the files attached to this post.
Last edited by gailb on 04 Jul 2021, 15:04, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sum list to get hierarchy

Post by HansV »

I don't understand - there are formulas in H2 and I2, but when I fill them down, they don't produce the results you now have.
What are the formulas supposed to do? Please explain for someone who doesn't have the slightest idea.
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Sum list to get hierarchy

Post by gailb »

Sorry about that. In row 2, everybody works for MXA. So in H:K, this would be the total.

In Row 3, everybody from Row 3 thru Row 9 works for MXAC. In Row 3 H:K, this should hold that total.

In Row 10, everybody from Row 10 thru Row 16 works for MXAD. In Row 10 H:K, this should hold that total.

So basically, I'm trying to get a recap of the total for each level in the organization structure.

Row 7 and Row 14 are another row where there should be a rolled up total since they have people working under them.

I hope this helps.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sum list to get hierarchy

Post by HansV »

I still don't understand.
The sum of everything in column C where column B starts with MXAC is 2. This is the value of C3, and it is reflected in H3.
The sum of everything in column D where column B starts with MXACA is 46. This is the value of D4, but it is NOT reflected in I4. Why not?
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Sum list to get hierarchy

Post by gailb »

It's because nobody works for MXACA. There are a standalone work center.

As for MXAC, everybody with MXAC in the office (column B) works for them so it's rolled up as a total in H:K.

I also replace the attachment in post #1 as I realized I had the formula wrong in column K.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sum list to get hierarchy

Post by HansV »

I came up with this for H2:

=IF(COUNTIF($B3:$B$16,$B2&"*")=0,"",SUMIF($B2:$B$16,$B2&"*",C2:C$16))

Fill to the right to column I, then down to row 16 (or vice versa).
If you don't like the zeros, apply the custom number format 0;;; to H2:K16.
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Sum list to get hierarchy

Post by gailb »

Hi Hans,

This works great. I make a slight modification to the Countif part as it was picking up a result for row 16 when it should not have.

I did

$B3:$B$17 versus $B3:$B$16

and it worked great.

Thanks