Generisk formel
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Sammendrag
For å lage en sammendragstabell med et antall sammenkoblede elementer som vises i en liste over eksisterende kombinasjoner, kan du bruke en hjelpekolonne og en formel basert på COUNTIFS-funksjonen. I eksemplet vist er formelen i celle H5:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
hvor "hjelper" er det navngitte området E5: E24.
Merk: denne formelen forutsetter at elementene ikke gjentas i en gitt kombinasjon (dvs. AAB, EFE er ikke gyldige kombinasjoner).
Forklaring
Vi vil telle hvor ofte elementer i kolonne B, C og D vises sammen. For eksempel hvor ofte A vises med C, B vises med F, G vises med D, og så videre. Dette virker som en perfekt bruk av COUNTIFS, men hvis vi prøver å legge til kriterier på jakt etter to elementer på tvers av 3 kolonner, fungerer det ikke.
En enkel løsning er å koble alle elementene sammen i en enkelt celle, og deretter bruke COUNTIFS med et jokertegn for å telle elementer. Vi gjør det med en hjelpekolonne (E) som forbinder elementene i kolonnene B, C og D ved å bruke CONCAT-funksjonen slik:
=CONCAT(B5:D5)
I eldre versjoner av Excel kan du bruke en formel som denne:
=B5&C5&D5
Fordi gjentatte elementer ikke er tillatt i en kombinasjon, ekskluderer den første delen av formelen samsvarende elementer. Hvis de to elementene er like, returnerer formelen en bindestrek eller bindestrek som tekst:
=IF($G5=H$4,"-"
Hvis varene er forskjellige, kjøres en COUNTIFS-funksjon:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS er konfigurert til å telle "par" av elementer. Bare når elementet i kolonne G og det tilsvarende elementet fra rad 4 vises sammen i en celle, telles paret. Et jokertegn (*) er sammenkoblet til begge sider av varen for å sikre at en kamp telles uansett hvor den vises i cellen.
