Ontbrekende waarden tellen

Stel dat u twee lijsten hebt en u het totale aantal waarden in één lijst wilt tellen dat niet voorkomt in de andere lijst. Hiervoor kunt u een SOMPRODUCT-formule gebruiken met de functies VERGELIJKEN en ISNB, of de AANTAL.ALS-functie.

Ontbrekende waarden tellen met SOMPRODUCT, VERGELIJKEN en ISNB
Ontbrekende waarden tellen met SOMPRODUCT en AANTAL.ALS

Ontbrekende waarden tellen met SOMPRODUCT, VERGELIJKEN en ISNB

Om het totale aantal waarden in lijst B te tellen dat ontbreekt in lijst A, zoals hierboven wordt weergegeven, gebruikt u eerst de functie VERGELIJKEN om een matrix te krijgen met de relatieve posities van de waarden uit lijst B in lijst A. Als een waarde niet voorkomt in lijst A, retourneert de functie een #N/B-fout. Vervolgens identificeert ISNB deze #N/B-fouten, en telt SOMPRODUCT het totale aantal fouten.

Algemene syntaxis

=SUMPRODUCT(--ISNA(MATCH()))range_to_count,lookup_range,0)))

Om het totale aantal waarden in lijst B te tellen dat ontbreekt in lijst A, kopieert of typt u de onderstaande formule in cel H6 en drukt u op Enterom het resultaat te verkrijgen:

=SOMPRODUCT(--ISNB(VERGELIJKEN()))F6:F8,B6:B10,0)))

Uitleg van de formule

=SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0)))

• VERGELIJKEN(F6:F8;B6:B10;0): Het overeenkomsttype 0 zorgt ervoor dat de functie VERGELIJKEN numerieke waarden retourneert die de relatieve posities aangeven van de waarden in de cellen F6 tot F8 binnen het bereik B6:B10. Als een waarde niet voorkomt in lijst A, wordt een #N/B-fout geretourneerd. De resultaten verschijnen daarom in een matrix zoals deze: {2;3;#N/B}.
• ISNB()VERGELIJKEN(F6:F8;B6:B10;0))=ISNB(){2;3;#N/B}):De functie ISNB bepaalt of een waarde de foutmelding „#N/B” is. Als dat zo is, retourneert de functie WAAR; zo niet, dan ONWAAR. De ISNB-formule levert dus {ONWAAR;ONWAAR;WAAR}.
• SOMPRODUCT(--)ISNB()VERGELIJKEN(F6:F8;B6:B10;0))) = SOMPRODUCT(--{ONWAAR;ONWAAR;WAAR}): Het dubbele minteken zet WAAR-waarden om naar 1 en ONWAAR-waarden naar 0: {0;0;1}. Vervolgens geeft de SOMPRODUCT-functie de som terug: 1.

Ontbrekende waarden tellen met SOMPRODUCT en AANTAL.ALS

Om het totale aantal waarden in lijst B te tellen dat ontbreekt in lijst A, kunt u de functie AANTAL.ALS gebruiken om te controleren of een waarde al dan niet voorkomt in lijst A, met de voorwaarde „=0” — want wanneer een waarde ontbreekt, levert AANTAL.ALS precies 0 op. SOMPRODUCT telt vervolgens het totale aantal ontbrekende waarden.

Algemene syntaxis

=SUMPRODUCT(--(COUNTIF()))lookup_range,range_to_count)=0))

Om het totale aantal waarden in lijst B te tellen dat ontbreekt in lijst A, kopieert of typt u de onderstaande formule in cel H6 en drukt u op Enterom het resultaat te verkrijgen:

=SOMPRODUCT(--(AANTAL.ALS()))B6:B10,F6:F8)=0))

Uitleg van de formule

=SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0))

• AANTAL.ALS(B6:B10;F6:F8):De functie AANTAL.ALS telt hoe vaak de waarden in de cellen van F6 tot F8 voorkomen in het bereik B6:B10. Het resultaat is een matrix zoals deze: {1;1;0}.
• --()AANTAL.ALS(B6:B10;F6:F8)=0)=--(){1;1;0}=0):Het fragment {1;1;0}=0 levert een matrix op van WAAR en ONWAAR: {ONWAAR;ONWAAR;WAAR}. Het dubbele minteken zet vervolgens WAAR-waarden om naar 1 en ONWAAR-waarden naar 0. De uiteindelijke matrix is als volgt: {0;0;1}.
• SOMPRODUCT()--()AANTAL.ALS(B6:B10;F6:F8)=0)) = SOMPRODUCT({0;0;1}): SOMPRODUCT geeft vervolgens de som terug: 1.

Gerelateerde functies

Gerelateerde formules