Generieke formule
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Samenvatting
Om een overzichtstabel samen te stellen met een aantal gepaarde items die in een lijst met bestaande combinaties voorkomen, kunt u een hulpkolom en een formule gebruiken op basis van de AANTAL.ALS-functie. In het getoonde voorbeeld is de formule in cel H5:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
waarbij "helper" het benoemde bereik E5: E24 is.
Opmerking: deze formule gaat ervan uit dat items niet worden herhaald in een bepaalde combinatie (dwz AAB, EFE zijn geen geldige combinaties).
Uitleg
We willen tellen hoe vaak items in de kolommen B, C en D samen voorkomen. Bijvoorbeeld: hoe vaak wordt A weergegeven met C, B wordt weergegeven met F, G wordt weergegeven met D, enzovoort. Dit lijkt een perfect gebruik van COUNTIFS, maar als we proberen criteria toe te voegen die zoeken naar 2 items in 3 kolommen, gaat het niet werken.
Een eenvoudige oplossing is om alle items in één cel samen te voegen en vervolgens AANTAL.ALS met een jokerteken te gebruiken om items te tellen. We doen dat met een hulpkolom (E) die items in kolommen B, C en D samenbrengt met behulp van de CONCAT-functie als volgt:
=CONCAT(B5:D5)
In oudere versies van Excel kunt u een formule als deze gebruiken:
=B5&C5&D5
Omdat herhaalde items niet zijn toegestaan in een combinatie, worden overeenkomende items uitgesloten in het eerste deel van de formule. Als de twee items hetzelfde zijn, retourneert de formule een koppelteken of liggend streepje als tekst:
=IF($G5=H$4,"-"
Als items verschillend zijn, wordt een COUNTIFS-functie uitgevoerd:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS is geconfigureerd om "paren" items te tellen. Alleen wanneer het item in kolom G en het corresponderende item uit rij 4 samen in een cel verschijnen, wordt het paar geteld. Een jokerteken (*) wordt aan beide zijden van het item samengevoegd om ervoor te zorgen dat een overeenkomst wordt geteld, ongeacht waar deze in de cel voorkomt.
