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:

Code:

---

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:

Code:

---

=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

Is this what you are looking for?

Code:

---

=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).

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

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.

Hhahaha that's insane.