Sumproduct with certain conditions

Hi All,

Can someone help me with regards to the formula i am using or can you guys give me another idea?
So here's my problem:

I have set of customers that will be removed in the system. And the customers has partners that i need to check if it will be remove from the system as well.

customer.JPG


From column AT to AY, these are the data extracted data that i need to us in order to validate the results.
Column AR is my formula.
Column AS is the one who check if the customers of the external partner is active. (1 = Active, Blank = Not Active)

FIRST SCENARIO: As you can see, the BP partner (AAA) is linked to Customer 3 with different country, Distribution Channel and division and is acting as the BP partner also.


Partner.JPG


Hence, if this is the scenario then the output should be MFD Selected (meaning we will only delete it on the Selected country of the customer 1)


Selected.JPG

SECOND: If the BP Partner is linked to another customer but has country, Distribution Channel and division, then the output should be = Just Unlink


Selected.JPG

THIRD: If the BP Partner is linked to other customer but the customer is already deleted(if the output in column AS = Blank) , then output should be "X".

X.JPG

My formula is actually working on the last two scenario but i have to add the first Scenario which is to MFD Selected..

Hope someone can help me with the formula.

Please Login or Register  to view this content.

Hi Ankur,

For the country,i will not only be US. And using the formula, i am geteing just unlink - it should be MFD SSA Thanks for your help.