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

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