Additional criteria for SUMPRODUCT

[BostonEJ]

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.

EJ

 

[baitmaster]

welcome to the board

the following link makes useful reading on this subject, I suspect it will be helpful with your question

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[baitmaster]

you might also investigate DFUNCTIONS: DSUM, DCOUNT, etc. Look them up in Excel's help files

 

[BostonEJ]

you might also investigate DFUNCTIONS: DSUM, DCOUNT, etc. Look them up in Excel's help files

Thank you for the link on your previous post. I am now more familiar with SUMPRODUCT but have not see a solution to the issue I have. I'll check out the other functions you noted as well to see if I can find something there.

Thanks again.

EJ