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

lacmkb 

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

=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.