I need help with a nested IF AND statement that also uses the SUBSTITUTE formula.

I’m trying to automate project ID creation. Each project is tied to a client and can also (optionally) be tied to a different sub-entity.

Depending on who is paying, I either want to use the Client name or the Sub-entity name in the project ID. Additionally, I want to use the abbreviation for each as long as one exists. If an abbreviation doesn’t exist, I want to use the name but remove " " and “,” using the substitute function.

So the scenarios are:

Scenario #1: Payer=“Client” AND Client Abbreviation is Blank, Client Name with " " and “,” removed with SUBSTITUTE function.

Scenario #2: Payer=“Client” AND Client Abbreviation is Not Blank, Client Abbreviation.

Scenario #3: Payer=“Sub-Entity” AND Sub-Entity Abbreviation is Blank, Sub-Entity Name with " " and “,” removed with SUBSTITUTE function.

Scenario #4: Payer=“Sub-Entity” AND Sub-Entity Abbreviation is Not Blank, Sub-Entity Abbreviation.

Any help would be greatly appreciated!