Consulting

Results 1 to 5 of 5

Thread: Sumproduct forumla needed for complex sum.

  1. #1

    Sumproduct forumla needed for complex sum.

    I'm pretty sure this can be done with sumproduct, but I can't quite get my head around it. I'd appreciate any help with this.

    I have two tables, Tbl1 A & B, Tbl2 E & F. Both are limited to 15 rows. The attached WB is a representation of the data.

    What I'm trying to do is create a sum that is the total amount budgeted. Tbl2 is the level 1 budget,Tbl1 is a level 2 budget. Each item in Tbl2 might be broken down into one or more items in Tbl1. For example, for code 100, in Tbl2, the budget is $300, and there are two sub items in tbl1 in rows 10 & 11 of $100 and $25.

    The above data shows all the possible combination of codes and dollar amounts (I believe).

    - Dollar amounts are optional in all cases.
    - Code cells may be empty in tbl1.
    - All codes in tbl1 are in sorted order in tbl2. Tbl2 may have codes that aren't in Tbl1. Ex: 130

    The above data should sum as follows:

    Sum(F10:F20) + (B12 + B14) + (B13 + B16) = 
    $750.00 + $155.00 + $175.00 = $1085.00
    - B12 & B14 are added because no code is in A
    - B13 & B16 are added because no amount is in tbl2 for code 120.
    - B10, B11, B15 are not added because their codes exist in tbl2 and have an amount.

    I have cross-posted a similar version of this in the MS Excel Forum under Programming. Sumproduct forumla for complex sum.

    I've gotten a partial solution that works except it can't pick up the (B13 + B16) portion. That formula is:

    =SUM($F$11:$F$20)+SUMPRODUCT(($B$11:$B$20)*(COUNTIF($E$11:$E$20,$A$11:$A$20)=0))
    (Thanks to Peter T and p45cal)

    All ideas are welcome, even different ways to specify the data to make this work.

    John

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Is this what you are looking for?

    =SUM($F$10:$F$19)
    +SUMPRODUCT(--(NOT(COUNTIF($E$10:$E$19,$A$10:$A$19)>0)),$B$10:$B$19)
    +SUMPRODUCT(($F$10:$F$19="")*(SUMIF($A$10:$A$19,$E$10:$E$19,$B$10:$B$19)))
    I just added onto p45cal's formula to pick up the missing bits (as I understand it).
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Hello XLD,

    That formula looks perfect for my needs! I've run some initial tests and it's working just the way I want.

    Thanks a million. I've been struggling with it for a while.

    DocBrown

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Great, I hope it works on all cases.

    FYI, I am moving this post to the SUMPRODUCT sub-forum, it helps keep the items together.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Tutor Benzadeus's Avatar
    Joined
    Dec 2008
    Location
    Belo Horizonte, Brazil
    Posts
    271
    Location
    Hhahaha that's insane.

Posting Permissions

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