Generisk formel
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Sammanfattning
För att räkna celler som inte är lika med någon av många saker kan du använda en formel baserad på MATCH, ISNA och SUMPRODUCT-funktionerna. I exemplet som visas är formeln i cell F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
där "data" är det namngivna området B5: B16 och "exkludera" är det namngivna området D5: D7.
Förklaring
Först ett litet sammanhang. Normalt, om du bara har ett par saker du inte vill räkna med, kan du använda COUNTIFS så här:
=COUNTIFS(range,"apple",range,"orange")
Men det går inte så bra om du har en lista med många saker, för du måste lägga till ett extra intervall / kriteriepar för varje sak du inte vill räkna med. Det skulle vara mycket lättare att skapa en lista och skicka in en referens till den här listan som en del av kriterierna. Det är precis vad formeln på den här sidan gör.
I grunden använder denna formel MATCH-funktionen för att hitta celler som inte är lika med "a", "b" eller "c" med detta uttryck:
MATCH(data,exclude,0)
Observera att uppslagsvärde och uppslagsmatris är "omvända" från normal konfiguration - vi tillhandahåller alla värden från det angivna intervallet "data" som uppslagsvärden och ger alla värden som vi vill utesluta i det angivna intervallet "utesluter". Eftersom vi ger MATCH mer än ett uppslagsvärde får vi mer än ett resultat i en sådan matris:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
I grund och botten ger MATCH oss positionen för matchande värden som ett nummer och returnerar # N / A för alla andra värden.
# N / A-resultaten är de vi är intresserade av, eftersom de representerar värden som inte är lika med "a", "b" eller "c". Följaktligen använder vi ISNA för att tvinga dessa värden till SANT och siffrorna till FALSE:
ISNA(MATCH(data,exclude,0)
Sedan använder vi en dubbel negativ för att tvinga TRUE till 1 och FALSE till noll. Den resulterande matrisen i SUMPRODUCT ser ut så här:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Med bara en matris att bearbeta summerar SUMPRODUCT och returnerar slutresultatet, 4.
Obs: Att använda SUMPRODUCT istället för SUM undviker behovet av att använda kontroll + shift + enter.
Räkna minus matchning
Ett annat sätt att räkna celler som inte är lika med någon av flera saker är att räkna alla värden och subtrahera matchningar. Du kan göra detta med en sådan formel:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Här returnerar COUNTA ett antal av alla icke-tomma celler. COUNTIF-funktionen, med det angivna intervallet "exkludera", returnerar tre räkningar, en för varje objekt i listan. SUMPRODUCT adderar summan och detta tal subtraheras från antalet icke-tomma celler. Det slutliga resultatet är antalet celler som inte motsvarar värdena i "utesluta".
Literal innehåller typlogik
Formeln på denna sida räknas med "lika med" logik. Om du behöver räkna celler som inte innehåller många strängar, där innehåller betyder att en sträng kan visas var som helst i en cell, behöver du en mer komplex formel.
