Generisk formel
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Resumé
For at tælle celler, der ikke svarer til nogen af mange ting, kan du bruge en formel baseret på MATCH, ISNA og SUMPRODUCT-funktionerne. I det viste eksempel er formlen i celle F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
hvor "data" er det navngivne område B5: B16 og "ekskluder" er det navngivne område D5: D7.
Forklaring
Først en lille kontekst. Normalt, hvis du kun har et par ting, du ikke vil tælle, kan du bruge COUNTIFS som denne:
=COUNTIFS(range,"apple",range,"orange")
Men dette skaleres ikke særlig godt, hvis du har en liste over mange ting, fordi du bliver nødt til at tilføje et ekstra interval / kriteriepar til hver ting, du ikke vil tælle. Det ville være meget lettere at oprette en liste og videregive en henvisning til denne liste som en del af kriterierne. Det er præcis, hvad formlen på denne side gør.
I kernen bruger denne formel MATCH-funktionen til at finde celler, der ikke er lig med "a", "b" eller "c" med dette udtryk:
MATCH(data,exclude,0)
Bemærk, at opslagsværdien og opslagsarray er "omvendt" fra normal konfiguration - vi angiver alle værdier fra det navngivne interval "data" som opslagsværdier, og giver alle værdier, vi vil ekskludere i det navngivne interval "ekskluder". Fordi vi giver MATCH mere end en opslagsværdi, får vi mere end et resultat i en matrix som denne:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
I det væsentlige giver MATCH os placeringen af matchende værdier som et tal og returnerer # N / A for alle andre værdier.
# N / A-resultaterne er dem, vi er interesserede i, da de repræsenterer værdier, der ikke er lig med "a", "b" eller "c". Derfor bruger vi ISNA til at tvinge disse værdier til SAND og tallene til FALSK:
ISNA(MATCH(data,exclude,0)
Derefter bruger vi en dobbelt negativ til at tvinge SAND til 1 og FALSK til nul. Den resulterende matrix inde i SUMPRODUCT ser sådan ud:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Med kun en matrix at behandle, summerer SUMPRODUCT og returnerer det endelige resultat, 4.
Bemærk: Brug af SUMPRODUCT i stedet for SUM undgår behovet for at bruge kontrol + shift + enter.
Tæl minus match
En anden måde at tælle celler, der ikke er lig med nogen af flere ting, er at tælle alle værdier og trække matches. Du kan gøre dette med en formel som denne:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Her returnerer COUNTA et antal af alle ikke-tomme celler. COUNTIF-funktionen, givet det navngivne interval "ekskluder", returnerer tre tællinger, en for hvert element på listen. SUMPRODUCT tilføjer summen, og dette tal trækkes fra antallet af alle ikke-tomme celler. Det endelige resultat er antallet af celler, der ikke har samme værdier i "ekskluder".
Bogstaveligt indeholder typelogik
Formlen på denne side tæller med "svarer til" logik. Hvis du har brug for at tælle celler, der ikke indeholder mange strenge, hvor indeholder betyder, at en streng kan vises overalt i en celle, skal du bruge en mere kompleks formel.
