Sum list to get hierarchy
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Sum list to get hierarchy
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.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sum list to get hierarchy
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.
What are the formulas supposed to do? Please explain for someone who doesn't have the slightest idea.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Sum list to get hierarchy
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.
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.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sum list to get hierarchy
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?
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
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Sum list to get hierarchy
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.
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.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sum list to get hierarchy
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.
=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
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Sum list to get hierarchy
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
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