Need formula help: Counting missed booking opportunities with agents owing money (complex data)

[lacmkb]

Hi all! 

I'm struggling to create a formula in Excel for a report on potential missed booking opportunities with agents who have a past due balance greater than 90 days. I have an AR Aging, Registered Orders (Origin and Destination), All cities, and an All Agents sheets. 

I am trying to match the Booked Agent(s) - Origin column in the AR Aging sheet with the agent on the All Agents sheet to return how many times we booked with an agent that wasn't the one with a past due balance. I need a formula that:

Matches the "Booked Agent(s) - Origin" column in the AR Aging sheet with agents in the All Agents sheet (even if there are multiple agents separated by semicolons)

Returns the total number of shipments booked with each agent listed in "Booked Agent(s) - Origin," not just the first one.

Handles cases where no match is found 

I've tried various formulas like INDEX/MATCH, SUMIF, and SUMPRODUCT, but I'm struggling to achieve the desired outcome.

Any help or alternative formulas to achieve this would be greatly appreciated! Thanks!

Here is a link to my document: https://1drv.ms/x/s!Aq6_DXyXC0ISqxjXJzBHGSd_CwFZ?e=rV619N 

I am using Microsoft Office LTSC Professional Plus 2021 on a PC.

[#2]

@lacmkb 

Analyzing the information you provided, here's a formula that addresses your requirements for counting missed booking opportunities with agents owing money:

Excel

=SUMPRODUCT(--(COUNTIF(FILTERXML("<x><t>"&SUBSTITUTE(A2,";","</t><t>")&"</t></x>", "//t"),B$2:B$100)>0),--(C$2:C$100>90))

Explanation:

1. A2: Replace this with the cell reference containing the "Booked Agent(s) - Origin" value in the AR Aging sheet.
2. B$2:B$100: Replace this with the range containing agent names in the All Agents sheet.
3. C$2:C$100: Replace this with the range containing the past due balance information in the All Agents sheet.

Breakdown:

• SUMPRODUCT: This function multiplies corresponding elements from two arrays and returns the sum of those products.
• **--: Double negation ensures logical TRUE becomes 1 and FALSE becomes 0 for calculations.
• COUNTIF(FILTERXML(...),...): This part counts the occurrences of each agent in the "Booked Agent(s) - Origin" value (split by semicolons using SUBSTITUTE) within the list of agents in the All Agents sheet.
  • FILTERXML: Extracts specific elements from an XML string representation of the split agent list.
  • COUNTIF: Counts the number of times each agent appears in the filtered list.
• --(C$2:C$100>90): This checks if the corresponding agent in the All Agents sheet has a past due balance greater than 90 days.

How it works:

1. The formula iterates through each agent in the "Booked Agent(s) - Origin" value (split by semicolons).
2. It checks if the agent exists in the list of agents on the All Agents sheet.
3. If a match is found and the agent has a past due balance, the corresponding product becomes 1 (TRUE * TRUE). Otherwise, it becomes 0.
4. SUMPRODUCT sums these products, resulting in the total count of missed booking opportunities with agents owing money.

Additional notes:

• This formula assumes your data starts in row 2 for both sheets. Adjust the ranges accordingly if your data starts in different rows.
• The formula handles cases where no match is found in the All Agents sheet by returning 0 for that agent.

Alternative approach:

If you have Microsoft 365 Excel, you can consider using the TEXTSPLIT function to split the "Booked Agent(s) - Origin" value and then use FILTER and SUM to achieve the desired outcome. However, the provided formula works for all Excel versions.

By implementing this formula, you can effectively calculate the missed booking opportunities with agents having past due balances, providing valuable insights for improving your business strategies.