Thread: Sumproduct forumla needed for complex sum.

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  Reply With Quote

2. 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).  Reply With Quote

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  Reply With Quote

4. 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.  Reply With Quote

5. Hhahaha that's insane.  Reply With Quote Posting Permissions

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