Flag intercompany balances for elimination (COUNTIF)
The problem
Problem statement
A balance is intercompany when the counterparty in B2:B5 is itself one of the group entities in A2:A5. Put "Eliminate" or "External" for row 2 in the Treatment cell D2.
The test is against the whole entity list, not just the row's own entity.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Entity | Counterparty | Amount | Treatment |
| 2 | North Ltd | South Ltd | 50000 | |
| 3 | North Ltd | Acme | 12000 | |
| 4 | South Ltd | North Ltd | 50000 | |
| 5 | East Ltd | Acme | 8000 |
Notes
COUNTIF against the entity column answers "is this counterparty inside the group?". It is the same shape as any membership test, and it keeps the rule in one place — the list of entities — instead of hardcoding names inside an IF.
How to approach
Your answer goes in D2.
- Target cell is D2
- Count how often B2 appears in A2:A5
- More than zero means it is in the group
- Shape: =IF(COUNTIF(?, ?)>0, ?, ?)
Function guide
FAQ
How do I identify intercompany balances?
=IF(COUNTIF(entities, counterparty)>0, "Eliminate", "External"), so the group's entity list is the single source of truth.
Why is a group company showing as external?
The name differs — an abbreviation, a legal suffix, or a trailing space. Match on a coded entity ID rather than the name when you can.