PDA

View Full Version : [SOLVED] Sumproduct forumla needed for complex sum.



DocBrown
09-16-2009, 06:16 PM
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

Bob Phillips
09-21-2009, 05:44 AM
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).

DocBrown
09-21-2009, 08:01 AM
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

Bob Phillips
09-21-2009, 08:03 AM
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.

Benzadeus
09-22-2009, 07:19 AM
Hhahaha that's insane.