Count ifs that will determine if the customer will be removed in the system

Hi All,

I still have the same problem i have encountered. I have actually formulated a count ifs formula but it has to be corrected.
the first check is i have to check (formula in column O) if the customer is active, if its active i can proceed with the below.


1. I have customers in Column. As extracted (Column Q to V), these customers are linked to only to given customer number in column B.
If this is the case, then the remarks should be "X" meaning i can delete the customer partner in the system.

Like below:

Attachment 408593

2. The second scenario is this: The customer in Column G is linked to other customer which is not in column B.
hence the results should be "just unlink". Like below:

2.JPG


I have formulated a count ifs formula but i think its missing something.

Please Login or Register  to view this content.

Can somebody help me with this? I've been stuck with this for weeks already.
Attached is the file.

See if this solves the issue:

Countifs formula to be corrected.xlsx

I changed the criteria ranges that are shown in red, since there are no values in those ranges as far as I can tell. I assumed the correct ranges as those were the only ranges containing the criteria.
Hope this helps.

Hi JeteMC,

Thanks for your inputs. I wonder how to make it work if all are included in the list?
I mean the main customer where its linked (Column AT) is also included in the column B list?

Thanks

I opened your file and could not find anything in Column AT.

Sorry JeteMC, it should be on column Q.

If the customers in column B is actually included on column Q, it should be X.

Hope youc an still help me oyut!

What should the formula display if the customer is not active, that is if there is not a 1 on column O for that customer?

hI JeteMc ,

It should be "X".

If my understanding of the spreadsheet is correct, you want to find if the customer in column B is active and if the number of occurrences of "partner" in column G matches the number of occurrences in column V. If those conditions are met display an X and if those conditions are not met display "Just Unlink". The attached file makes use of the following formula to accomplish that task:

Please Login or Register  to view this content.

Copy of Countifs formula to be corrected.xlsx

Let me know if you have any questions.

Forgot the added criteria that even if the number of occurrences of "partner" in column G does not match the number of occurrences in column V, if the customer in column B is inactive, display "X".

Please Login or Register  to view this content.

Copy of Countifs formula to be corrected.xlsx

Let me know if you have any questions.

Thanks for that JeteMc! Totally worked...

But i have here another scenario that is possible i just noticed it now


1.JPG


So as you can see, customer HHH is linked with customer 666 based on the extracted data.
And customer 666 is already has the deletion flag (X) hence the result should be "X".

Is it possible also to add that on the formula?
Thank you so much.

To account for the new scenario change the formula to:

Please Login or Register  to view this content.

Copy of Countifs formula to be corrected.xlsx

Let me know if you have any questions.

Paste this code in M4 and copy down:

Please Login or Register  to view this content.

Let me know if you have any questions.

Hi Jete,

i really appreciate your response on this.. i really owe you alot ^^

This is the last scenario which i think the hardest:

As you can see below, the external BP Partner ABC is linked to two customers with the same sales org, dc and division.

Capture.JPG

If this is the scenario, the result should be JUST Unlink since they exist in same sales org dc and dvision.

How ever, the external BP Customer ABC is linked to another customer AAA under DK01 14/10.

Capture.JPG

Since customer AAA is given on the list of main customers, the tagging for the external BP ABC should be MFD SSA since it is linked to another active customer under FR01 14/10. Meaning it will be deleted under DK01 only and not FR01.

Im so sorry this is the last scenario.. i tried to alter the formula you gave but i just cant figure it out..

Hope you can help me.

Hi Jete!

Thanks alot! That worked very well!
However, how can we reomve the declaration of FR01 since thre will be other sales org like PK01, PH01 and many more?