Generieke formule
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Samenvatting
Om cellen te tellen die niet gelijk zijn aan een van de vele dingen, kunt u een formule gebruiken die is gebaseerd op de MATCH-, ISNA- en SUMPRODUCT-functies. In het getoonde voorbeeld is de formule in cel F5:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
waarbij "data" het benoemde bereik B5: B16 is en "uitsluiten" het benoemde bereik D5: D7.
Uitleg
Eerst een kleine context. Normaal gesproken, als u slechts een paar dingen heeft die u niet wilt tellen, kunt u COUNTIFS als volgt gebruiken:
=COUNTIFS(range,"apple",range,"orange")
Maar dit schaalt niet erg goed als je een lijst met veel dingen hebt, omdat je een extra bereik / criteriumpaar moet toevoegen voor elk ding dat je niet wilt tellen. Het zou veel gemakkelijker zijn om een lijst samen te stellen en een verwijzing naar deze lijst door te geven als onderdeel van de criteria. Dat is precies wat de formule op deze pagina doet.
In de kern gebruikt deze formule de MATCH-functie om cellen te vinden die niet gelijk zijn aan "a", "b" of "c" met deze uitdrukking:
MATCH(data,exclude,0)
Merk op dat de opzoekwaarde en de opzoekmatrix "omgekeerd" zijn ten opzichte van de normale configuratie - we leveren alle waarden uit het benoemde bereik "data" als opzoekwaarden, en geven alle waarden die we willen uitsluiten in het benoemde bereik "uitsluiten". Omdat we MATCH meer dan één opzoekwaarde geven, krijgen we meer dan één resultaat in een array als deze:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
In wezen geeft MATCH ons de positie van overeenkomende waarden als een getal, en retourneert # N / A voor alle andere waarden.
De resultaten # N / A zijn degene waarin we geïnteresseerd zijn, aangezien ze waarden vertegenwoordigen die niet gelijk zijn aan "a", "b" of "c". Dienovereenkomstig gebruiken we ISNA om deze waarden naar WAAR te dwingen en de getallen naar FALSE:
ISNA(MATCH(data,exclude,0)
Vervolgens gebruiken we een dubbel negatief om TRUE naar 1 en FALSE naar nul te dwingen. De resulterende array, binnen SUMPRODUCT, ziet er als volgt uit:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Met slechts één array om te verwerken, telt SUMPRODUCT de som op en retourneert het een eindresultaat, 4.
Opmerking: als u SOMPRODUCT gebruikt in plaats van SOM, hoeft u control + shift + enter niet te gebruiken.
Tel minus wedstrijd
Een andere manier om cellen te tellen die niet gelijk zijn aan een van de verschillende dingen, is door alle waarden te tellen en overeenkomsten af te trekken. U kunt dit doen met een formule als deze:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Hier retourneert COUNTA een telling van alle niet-lege cellen. De AANTAL.ALS-functie, gegeven het benoemde bereik "uitsluiten", retourneert drie tellingen, één voor elk item in de lijst. SOMPRODUCT telt het totaal op en dit aantal wordt afgetrokken van het aantal niet-lege cellen. Het uiteindelijke resultaat is het aantal cellen dat niet gelijk is aan de waarden in "uitsluiten".
Letterlijk bevat typelogica
De formule op deze pagina telt met "gelijk aan" logica. Als u cellen moet tellen die niet veel tekenreeksen bevatten, waarbij bevat betekent dat een tekenreeks overal in een cel kan voorkomen, heeft u een complexere formule nodig.
