Generieke formule
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Samenvatting
Om de waarden in een lijst te tellen die ontbreken in een andere lijst, kunt u een formule gebruiken die is gebaseerd op de functies AANTAL.ALS en SOMPRODUCT.
In het getoonde voorbeeld is de formule in H6:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Wat 1 retourneert omdat de waarde "Osborne" niet voorkomt in B6: B11.
Uitleg
De AANTAL.ALS-functie controleert waarden in een bereik op basis van criteria. Vaak wordt er maar één criterium aangeleverd, maar in dit geval leveren wij meer dan één criterium.
Voor bereik geven we AANTAL.ALS de benoemde bereiklijst1 (B6: B11), en voor criteria geven we de benoemde bereiklijst2 (F6: F8).
Omdat we AANTAL.ALS meer dan één criterium geven, krijgen we meer dan één resultaat in een resultaatmatrix die er als volgt uitziet: (2; 1; 0)
We willen alleen waarden tellen die ontbreken, die per definitie nul tellen, dus converteren we deze waarden naar WAAR en ONWAAR met de "= 0" -instructie, die het volgende oplevert: (ONWAAR; ONWAAR; WAAR)
Vervolgens forceren we de TRUE FALSE-waarden naar 1s en 0s met de dubbel-negatieve operator (-), die produceert: (0; 0; 1)
Ten slotte gebruiken we SOMPRODUCT om de items in de array bij elkaar op te tellen en een totaal aantal ontbrekende waarden te retourneren.
Alternatief met MATCH
Als u de voorkeur geeft aan meer letterlijke formules, kunt u de onderstaande formule gebruiken, gebaseerd op MATCH, die letterlijk waarden telt die "ontbreken" met behulp van de ISNA-functie:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
