Všeobecný vzorec
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Zhrnutie
Ak chcete spočítať hodnoty v jednom zozname, ktoré chýbajú v inom zozname, môžete použiť vzorec založený na funkciách COUNTIF a SUMPRODUCT.
V zobrazenom príklade je vzorec v H6:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Ktorá vracia 1, pretože hodnota „Osborne“ sa v B6: B11 neobjavuje.
Vysvetlenie
Funkcie COUNTIF kontrolujú hodnoty v rozsahu podľa kritérií. Často sa dodáva iba jedno kritérium, ale v tomto prípade dodávame viac ako jedno kritérium.
Pre rozsah dáme COUNTIF pomenovaný zoznam rozsahov1 (B6: B11) a pre kritériá poskytneme pomenovaný rozsah zoznam2 (F6: F8).
Pretože dávame COUNTIF viac ako jedno kritérium, dostaneme viac ako jeden výsledok do výsledného poľa, ktoré vyzerá takto: (2; 1; 0)
Chceme spočítať iba chýbajúce hodnoty, ktoré majú podľa definície počet nula, takže tieto hodnoty prevedieme na TRUE a FALSE pomocou príkazu "= 0", ktorý prinesie: (FALSE; FALSE; TRUE)
Potom vynútime hodnoty TRUE FALSE na 1 s a 0 s dvojitým záporným operátorom (-), ktorý vytvorí: (0; 0; 1)
Nakoniec pomocou programu SUMPRODUCT spočítame položky v poli a vrátime celkový počet chýbajúcich hodnôt.
Alternatíva so ZÁPASOM
Ak dávate prednosť doslovnejším vzorcom, môžete použiť nasledujúci vzorec založený na MATCH, ktorý pomocou funkcie ISNA doslova počíta hodnoty, ktoré „chýbajú“:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
