Need additional function within SUMPRODUCT

[EJS]

Hello all,

I am familiar with SUMPRODUCT but not sure what other functions I need to use with it to achieve what I am looking for. I'm working with a spreadsheet of data I receive from an outside party.

I have one worksheet where I maintain defined ranges. In this example I have a list of ~20 FUNDS which make up the range "ASSETMNG1". A sample spreadsheet is attached with the other details. Another worksheet is the DATA I receive with columns (Fund -- Event -- Location -- DTC_INST -- EUR_INST -- FED_INST -- Net Amount). The final worksheet is a list of brokers and their instructions per location (DTC_INST, EURO_INST, FED_INST). Columns (Broker -- DTC -- EURO -- FED -- DELIVERIES -- RECEIPTS).

=SUMPRODUCT((DATA!$A$2:$A$8=ASSETMNG1)*(DATA!$B$2:$B$8="DELIVER")*(------------------------)*(DATA!$G$2:$G$8))

The blank area is where I'm trying to fill the void with logic like (sum the transactions on the DATA sheet that match this broker's DTC_INST, EUR_INST, or FED_INST)

The only other solution I can think of is a LONG formula that sum's 3 individual SUMPRODUCT's?

Thanks in advance for any assistance and let me know where I may need to clarify.

Also asked the question at http://www.mrexcel.com/forum/showthr...18#post2454918

EJ

[daddylonglegs]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

Please add a link, thanks

[EJS]

I have a suggestion of trying to use DSUM to accomplish what I'm looking to do. I read up on it and it doesn't seem to address my issue though I have never used DSUM before. So any additional suggestions is appreciated.

[daddylonglegs]

I'm not clear what results you expect. This might do it

=SUMPRODUCT((DATA!$A$2:$A$100=ASSETMNG1)*(DATA!$B$2:$B$100="DELIVER")*((DATA!$D$2:$D$100=B4)*(B4<>"")+(DATA!$E$2:$E$100=C4)*(C4<>"")+(DATA!$F$2:$F$100=D4)*(D4<>"")>0)*(DATA!$G$2:$G$100))

Note that I don't see ASSETMNG1 defined in your sheet, which funds should it include? You'll need to define the range before the formula will work

If that doesn't do it for you then can you detail the results you expect?

[EJS]

daddylonglegs,

I was able to make the formula you listed work for me. Thank you very much for your help.

EJ