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:
- B12 & B14 are added because no code is in ASum(F10:F20) + (B12 + B14) + (B13 + B16) = $750.00 + $155.00 + $175.00 = $1085.00
- 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:
(Thanks to Peter T and p45cal)=SUM($F$11:$F$20)+SUMPRODUCT(($B$11:$B$20)*(COUNTIF($E$11:$E$20,$A$11:$A$20)=0))
All ideas are welcome, even different ways to specify the data to make this work.
John