Aantal cellen tellen dat niet gelijk is aan meerdere waarden in Excel

In Excel bepaalt u eenvoudig het aantal cellen dat niet gelijk is aan een specifieke waarde met de functie AANTAL.ALS – maar hebt u ooit geprobeerd het aantal cellen te tellen dat niet overeenkomt met meerdere waarden? Stel bijvoorbeeld dat u het totale aantal producten in kolom A wilt bepalen, maar de specifieke items in C4:C6 wilt uitsluiten, zoals in de afbeelding hieronder. In dit artikel bespreek ik enkele formules om deze taak in Excel vlot op te lossen.

• Aantal cellen dat niet gelijk is aan meerdere waarden, formule 1
• Aantal cellen dat niet gelijk is aan meerdere waarden, formule 2

Aantal cellen dat niet gelijk is aan meerdere waarden, formule 1

Om cellen te tellen die niet overeenkomen met meerdere opgegeven waarden, combineert u de functies MATCH, ISNA en SOMPRODUCT. De algemene syntaxis is:

• range: De lijst met cellen die u wilt tellen;
• exclude_values: De waarden die u wilt uitsluiten uit de bereiklijst.

1. Voer de onderstaande formule in of kopieer deze naar een lege cel om het resultaat weer te geven:

2. Druk vervolgens op Enter om het juiste resultaat te verkrijgen, zie afbeelding:

Uitleg van de formule:

• MATCH(A2:A13;C2:C4;0): Deze MATCH-functie retourneert de positie van elke overeenkomende waarde als getal en geeft een #N/B-foutwaarde wanneer er geen overeenkomst is. Met andere woorden: als een waarde uit A2:A13 voorkomt in C2:C4, krijg je het bijbehorende positienummer; zo niet, dan verschijnt #N/B. Het resultaat is hier: {1;#N/B;#N/B;1;3;2;#N/B;#N/B;3;#N/B;#N/B;#N/B;}.
• ISNA(MATCH(A2:A13;C2:C4;0))= ISNA({1;#N/B;#N/B;1;3;2;#N/B; #N/B;3;#N/B; #N/B; #N/B;}): De ISNA-functie zet foutwaarden om in WAAR en getallen in ONWAAR, zodat u het volgende krijgt: {ONWAAR;WAAR;WAAR;ONWAAR;ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR;WAAR;WAAR;WAAR}.
• --(ISNA(MATCH(A2:A13;C2:C4;0))))=--({ONWAAR;WAAR;WAAR;ONWAAR;ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR;WAAR;WAAR;WAAR}): De dubbele mintekenoperator (--) zet alle WAAR-waarden om in 1 en alle ONWAAR-waarden in 0, zoals hier: SOMPRODUCT(--(ISNA(MATCH(A2:A13;C2:C4;0))))=SOMPRODUCT({0;1;1;0;0;0;1;1;0;1;1;1}).
• Vervolgens wordt de SOMPRODUCT-functie gebruikt om de getallen in de matrix op te tellen en zo het resultaat — 7 — te verkrijgen.

Aantal cellen dat niet gelijk is aan meerdere waarden, formule 2

Om deze taak in Excel aan te pakken, kunt u ook de krachtige combinatie van de functies AANTALARG, SOMPRODUCT en AANTAL.ALS gebruiken. De algemene syntaxis is:

• range: De lijst met cellen die u wilt tellen;
• exclude_values: De waarden die u wilt negeren in de bereiklijst.

1. Voer de onderstaande formule in of kopieer deze naar een lege cel om het resultaat te bekijken:

2. Druk vervolgens op Enter om het resultaat te verkrijgen, zoals in onderstaande afbeelding:

Uitleg van de formule:

• AANTALARG(A2:A13): Deze AANTALARG-functie telt het aantal niet-lege cellen in het bereik A2:A13. Het resultaat is: 12.
• AANTAL.ALS(A2:A13;C2:C4): Deze AANTAL.ALS-functie telt hoe vaak de waarden in bereik A2:A13 voorkomen die overeenkomen met de criteria in C2:C4. Het resultaat is {2;1;2}, wat betekent dat ‘Appel’ twee keer voorkomt, ‘Druif’ één keer en ‘Citroen’ twee keer.
• SOMPRODUCT(AANTAL.ALS(A2:A13;C2:C4))=SOMPRODUCT({2;1;2}): Deze SOMPRODUCT-functie telt alle waarden in de matrix op en levert het eindresultaat: 5.
• AANTALARG(A2:A13)-SOMPRODUCT(AANTAL.ALS(A2:A13;C2:C4))=12-5: Het resultaat (5) van de SOMPRODUCT-functie wordt afgetrokken van het totale aantal niet-lege cellen (12) om het gewenste eindresultaat te verkrijgen.

Opmerking: Beide bovenstaande formules zijn niet hoofdlettergevoelig.

Gebruikte gerelateerde functie:

Meer artikelen: