Generisk formel
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Sammendrag
For å telle unike verdier med en eller flere betingelser, kan du bruke en formel basert på UNIK og FILTER. I eksemplet vist er formelen i H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
som returnerer 3, siden det er tre unike navn i B6: B15 assosiert med Omega-prosjektet.
Merk: denne formelen krever dynamiske matriseformler, bare tilgjengelig i Excel 365. Med en eldre versjon av Excel kan du bruke mer komplekse alternative formler.
Forklaring
I kjernen bruker denne formelen UNIQUE-funksjonen til å trekke ut unike verdier, og FILTER-funksjonen bruker kriterier.
FILTER-funksjonen fungerer fra innsiden og ut og brukes til å anvende kriterier og trekke ut bare navn som er knyttet til "Omega" -prosjektet:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Resultatet fra FILTER er en matrise som dette:
("Jim";"Jim";"Carl";"Sue";"Carl")
Deretter brukes UNIQUE-funksjonen til å fjerne duplikater:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
som resulterer i en ny matrise som dette:
("Jim";"Carl";"Sue") // after UNIQUE
På dette punktet har vi en unik liste over navn knyttet til Omega, og vi trenger bare å telle dem. Av grunner som er forklart nedenfor, gjør vi dette med LEN-funksjonen og SUM-funksjonen. For å gjøre ting klart, skriver vi først om formelen for å inkludere den unike listen:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
LEN-funksjonen får lengden på hvert element i listen, og returnerer en rekke lengder:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Deretter sjekker vi om lengdene er større enn null:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Og bruk et dobbelt negativt for å tvinge de SANNE og FALSE verdiene til 1s og 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Til slutt legger vi opp resultatene med SUM-funksjonen:
=SUM((1;1;1)) // returns 3
Denne matrisen leveres direkte til COUNTA-funksjonen, som returnerer en endelig telling:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Merk at fordi vi sjekker lengden på hvert element som returneres av UNIK, ignoreres tomme eller tomme celler som oppfyller kriteriene. Denne formelen er dynamisk og beregnes på nytt umiddelbart hvis kildedataene endres.
Telle unikt med flere kriterier
For å telle unike verdier basert på flere kriterier, kan du utvide "inkluderer" -logikken i FILTER. Hvis du for eksempel bare vil telle unike navn for Omega-prosjektet i juni, bruker du:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Dette er et eksempel på bruk av boolsk logikk for å bruke mer enn én tilstand. Tilnærmingen blir forklart mer detaljert her.
For mer informasjon, se denne treningsvideoen: Hvordan filtrere med flere kriterier.
COUNTA
Det er mulig å skrive en enklere formel som svarer på COUNTA-funksjonen. En viktig advarsel er imidlertid at COUNTA vil returnere 1 når det ikke er noen samsvarende verdier. Dette er fordi FILTER-funksjonen returnerer en feil når ingen data samsvarer med kriteriene, og denne feilen ender opp med å telles av COUNTA-funksjonen. Den grunnleggende COUNTA-formelen ser slik ut:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Igjen vil denne formelen returnere 1 når det ikke er samsvarende data. Det vil også inkludere tomme celler som oppfyller kriteriene. Formelen basert på LEN og SUM er et bedre alternativ.
Ingen dynamiske matriser
Hvis du bruker en eldre versjon av Excel uten støtte for dynamisk matrise, kan du bruke en mer kompleks formel. For en mer generell diskusjon av dynamiske matriser, se: Alternativer til dynamiske matriseformler.
