sumproduct if question

[miller_ilya]

hey guys, first post to this site- i am having a hard time figuring out an issue in excel and was hoping you guys could help out


Broker security rate
BA a 1
BA b 2
CS a 3
CS c 2
DB f 1
DB g 2
GS a 3
GS b 4
GS c 1
GS d 2
GS e 1


I need something that will do the following- based off another cell lets say X1 and X2,

find all the securities, that broker X2 has within X1 securities' list and sums the rates (column M) for those securities for broker x2


I am working with a fomula =SUMPRODUCT(--(data!$A$2:$A$159361=Scorecard!$X$1))*SUMPRODUCT((data!$A$2:$A$159361=Scorecard!X$2)*(data!$M$2:$M$159361)

The formulas is not working properly so your help is appreciated

[Fotis1991]

Perhaps

=SUMPRODUCT((data!$A$2:$A$159361=Scorecard!$X$1)*(data!$A$2:$A$159361=Scorecard!X$2)*(data!$M$2:$M$159361))

[miller_ilya]

that doesnt work either, you first need to calculate the array that makes up X1, then find the subset of that array that comprises X2, then with this array that is basically an intersection of X1 AND X2 sum the rates

[Fotis1991]

Be sure that best way to describe your problem is to upload a sample workbook.

Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[miller_ilya]

excelforum_upload.xlsx

here you go

[Fotis1991]

Do you mean?

=SUMPRODUCT(($A$9:$A$32=$F$3)+($A$9:$A$32=$F$4)*($C$9:$C$32))

OR ?

=SUMPRODUCT(($A$9:$A$32=$F$3)*($C$9:$C$32))+SUMPRODUCT(($A$9:$A$32=$F$4)*($C$9:$C$32))

[miller_ilya]

netiher of those worked, essentially it should sum column c11,c22, and c29 because those 3 securities are in both GS and ML

In a small dataset this can be done manually, but the actual trade file is like 20,000 rows so i need a formula for this

thanks again for your help

[AlKey]

Try this for ML

=SUMPRODUCT(--($A$9:$A$32="ML")*($C$9:$C$32))

[miller_ilya]

That doesnt work because you never incorporate to check if GS is holdiing it. That formula just gives me the sum of all the rates for ML

I need the rates for ML only in the stocks held by GS

[Fotis1991]

...................

[miller_ilya]

GS- ALL Letters hahaha

[Fotis1991]

It's late for me....

I don't understand the logic..In my example, why all the rows that have true value in column E are not added but only the green cells??

[miller_ilya]

you are right only the highlighted green cells should be added together

the logic is as such- lets say there are 2 used car dealers
one has 5 car models, the second has 3 car models
only 2 of the models are the same for both dealers 1 and dealer 2
i am ultimately trying to find the total value of the cars with dealer 2 that are also held with dealer 1

[Fotis1991]

But in my example both brokers have 3 cars each one of them with the same code(column B)..

B3B8V9 244412 & B0XHV5

Why-which is the logic-to add only the green cells and not all of these OR only the others?

[miller_ilya]

adding only the green cells would yield the correct resut

[Fotis1991]

Must be a condition for doing that..Which is that condition?

[miller_ilya]

the reason whe you sum green is because ML is broker 2 and those 3 securities match the holdings of broker 1

[Fotis1991]

=sumproduct((a9:a32=f4)*(e9:e32=true)*(c9:c32))

[AlKey]

Please take a look at the attached file. It may not be exactly as you want it as it uses some helper rows to get to the correct answer.

[miller_ilya]

you guys are both awesome

thanks a ton for your help

[AlKey]

You're Welcome and if your issues is resolved please mark thread as "Solved"

[Fotis1991]

you guys are both awesome

thanks a ton for your help

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

[miller_ilya]

so guys apparently i oversimplified my initial explanation

in the attached sheet, its is actually a list of brokers that i need to do this calculation for

any help is appreciatedexcelforum_upload.xlsx

[Fotis1991]

I hope this time your explanations to don't be simplified!!

1 ugly way could be this.

I used these formulas.

Please Login or Register  to view this content.

ARRAY formula to get the results for eatch Broker.

Please Login or Register  to view this content.

To find which stock from eatch Broker are common with broker 1.

=SUM(K5:K12)

For the final result.

Comments?