sum with several matches

[Melinda]

I have a spreadsheet that
(1) I need to sum all in column L that matches B43(5033110000) in Column C in the SAP worksheet(Hidden). Total is $3,511.81
(2) I need to pull all the document #'s(3000012230, 3000015769, 3000016096, 3000017954 and 3200000712) that match B43 in Column C in the SAP worksheet. If these each one of these documents have account number in column C in the SAP worksheet that begins with a "2" then pull the negative $ amount (-$3,511.81) in Column L associated with the row that matches B43 in Column C.
(3) (1) - (2) = C43

For the last two days I have tried using sumif, if, index, match and sumproduct but cannot seem to get it to work right.

Can someone please come to the rescue?

Melinda

[Bob Phillips]

You've totally lost me. I see loads of formulae in that spreadsheet but have no idea what you need to do, and where.

What does pull mean in 2)? Give some examples.

[Melinda]

For account 5033110000 and grant P24017000109, the sum of all the 503311000 in the spreadsheet SAP is $3,511.81 and the formula I use is
IF(ISBLANK(E43)," ",IF(ISNA(+SUMPRODUCT(--(SAP!$B:$B=LEFT($A$1,12)),--(SAP!$C:$C=""&$B43),--(SAP!$K:$K<=$A$15),(SAP!$L:$L))),0,+SUMPRODUCT(--(SAP!$B:$B=LEFT($A$1,12)),--(SAP!$C:$C=""&$B43),--(SAP!$K:$K<=$A$15),(SAP!$L:$L)))).

However a need to include a formula that will subtract the sum of the dollar amounts in Column L where the accounts that start with a "2" in column C in the SAP worksheet that has a document number that matches any of the document numbers that have 5033110000 as an account number.

For example, the first account number 5033110000 has a document number of 3000012230. This document number 3000012230 also has an account number 2000010000 then I will need to pull the negative sum amount ($2350.33 + $45.05) associated with that document number and 5033110000.

Next, the next account number 5033110000 has a document number of 3000017954. This document number 3000017954 also has an account number 2000010000 then I will need to pull the negative sum amount ($903.14 + $126.67) associated with that document number and 5033110000.

The total that should appear in B43 should be 0. The total for 5033110000 is $3,511.81 then subtract the total for all the account number 5033110000 documents that have an account that start with a "2" is $3,511.81.

I am reattaching the file with the SAP worksheet resorted with the document numbers grouped together and the rows highlighted that I am referring to.

I hope you can help.