Generisk formel
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Sammendrag
For å telle celler som ikke er lik noen av mange ting, kan du bruke en formel basert på MATCH, ISNA og SUMPRODUCT-funksjonene. I eksemplet vist er formelen i celle F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
der "data" er det navngitte området B5: B16 og "ekskluder" er det navngitte området D5: D7.
Forklaring
Først en liten kontekst. Normalt, hvis du bare har et par ting du ikke vil telle, kan du bruke ANTALL som dette:
=COUNTIFS(range,"apple",range,"orange")
Men dette skaleres ikke veldig bra hvis du har en liste over mange ting, fordi du må legge til et ekstra utvalg / kriteriepar for hver ting du ikke vil telle. Det ville være mye lettere å lage en liste og sende inn en referanse til denne listen som en del av kriteriene. Det er nøyaktig hva formelen på denne siden gjør.
I kjernen bruker denne formelen MATCH-funksjonen til å finne celler som ikke er lik "a", "b" eller "c" med dette uttrykket:
MATCH(data,exclude,0)
Legg merke til at oppslagsverdien og oppslagsmatrisen er "reversert" fra normal konfigurasjon - vi gir alle verdier fra det nevnte området "data" som oppslagsverdier, og gir alle verdiene vi vil ekskludere i det navngitte området "ekskluder". Fordi vi gir MATCH mer enn en oppslagsverdi, får vi mer enn ett resultat i en matrise som dette:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
I hovedsak gir MATCH oss posisjonen til å matche verdier som et tall, og returnerer # N / A for alle andre verdier.
# N / A-resultatene er de vi er interessert i, siden de representerer verdier som ikke er lik "a", "b" eller "c". Følgelig bruker vi ISNA til å tvinge disse verdiene til SANT, og tallene til FALSE:
ISNA(MATCH(data,exclude,0)
Deretter bruker vi et dobbeltnegativ for å tvinge SANT til 1 og FALSE til null. Den resulterende matrisen, inne i SUMPRODUCT, ser slik ut:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Med bare en matrise å behandle, summerer SUMPRODUCT og returnerer det endelige resultatet, 4.
Merk: Ved å bruke SUMPRODUCT i stedet for SUM unngås behovet for å bruke kontroll + shift + enter.
Telle minus kamp
En annen måte å telle celler som ikke er lik noen av flere ting, er å telle alle verdier og trekke treff. Du kan gjøre dette med en formel som denne:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Her returnerer COUNTA en telling av alle celler som ikke er tomme. COUNTIF-funksjonen, gitt det nevnte området "ekskluder", returnerer tre tellinger, ett for hvert element i listen. SUMPRODUCT legger opp summen, og dette tallet trekkes fra tellingen av alle ikke-tomme celler. Det endelige resultatet er antall celler som ikke tilsvarer verdiene i "ekskluderer".
Bokstavelig inneholder typelogikk
Formelen på denne siden teller med "tilsvarer" logikk. Hvis du trenger å telle celler som ikke inneholder mange strenger, hvor inneholder betyr at en streng kan vises hvor som helst i en celle, trenger du en mer kompleks formel.
