Summing the children...but only sometimes
Results 1 to 4 of 4

Thread: Summing the children...but only sometimes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Summing the children...but only sometimes

    I'm trying to build a pivot table that will allow the user to select one department and it will show all say 3 data fields for that department. In some cases, however, the department they select will have sub departments. There is data at both the department level and the sub department level which is making things difficult. I have a department table that has a relationship already set up (*) with the data table.
    My first issue is, if I do the filter on the department level (from the Dpt table) it wont show the sub departments in the filter. If I apply the filter to the Sub department level (Dpt table) it wont sum up the departments correctly. I'm not sure how to handle this?

    To make my life more difficult, when the user selects ones of the parent departments, the pivot table needs to show the sum of 2 of the fields but not the last one. I think I have to tell it to ignore the relationship so it wont do that? I haven't figured that part out yet...

    Dpt Table
    *Sub Department / Department
    Red / Red
    Pink / Red
    Magenta / Red
    Blue / Blue
    Turquoise / Blue

    Data Table
    *Department / Value1 / Value2 / Value3
    Red / 1 / 4 / 2
    Pink / 2 / 4 / 3
    Magenta / 2 / 4 / 0

    For example, if I were to say Pink I should get
    2 / 4 / 3

    If I were to say Red, I'm trying to get:
    5 / 12 / 2

  2. #2
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,843
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Summing the children...but only sometimes

    Hi,
    Maybe something like:
    Excel 2016 (Windows) 64 bit
    ABCDEFGHIJKL
    1subdeptdptv1v2v3v1v2v3
    2redredred142red5125
    3pinkredpink243
    4magentaredmagenta240
    5blueblue
    6turquoiseblue

    teddy0bear



    Array Formulas
    CellFormula
    J2{=IF(COUNTIF(dpt,$I2)=1,INDEX(E$2:E$4,MATCH($I2,$D$2:$D$4,0)),SUM(SUMIF(teddy0bear!$D$2:$D$4,IF(teddy0bear!$B$2:$B$6=$I2,teddy0bear!$A$2:$A$6),INDEX(teddy0bear!E$2:E$4,MATCH($I2,teddy0bear!$D$2:$D$4,0),0))))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Φ(𝑘)𝛼Α𝑡
    O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste

  3. #3
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Location
    seated
    Posts
    3,843
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Summing the children...but only sometimes

    First range is a table called dpt.
    That bit =IF(COUNTIF(dpt,$I2) here refers to range A2:B6
    The formula is truncated somehow in the previous post:
    CSE =IF(COUNTIF(dpt,$I2)=1,INDEX(E$2:E$4,MATCH($I2,$D$2:$D$4,0)),SUM(SUMIF(teddy0bear!$D$2:$D$4,IF(teddy0bear!$B$2:$B$6=$I2, teddy0bear!$A$2:$A$6),INDEX(teddy0bear!E$2:E$4,MATCH($I2,teddy0bear!$D$2:$D$4,0),0))))
    Φ(𝑘)𝛼Α𝑡
    O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste

  4. #4
    New Member
    Join Date
    Aug 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summing the children...but only sometimes

    That sounds like something I would do if I was doing regular excel tables. The data table is coming from an external source and only exists as a connection in my data model. I could maybe add a calculated column into the dta model like this but I'd worry about adapting for changes on the dpt (as someone will decide that pink is blue, just you watch) and I still have the issue about it filtering properly when changing the slicer.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •