Splošna formula
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Povzetek
Če želite na enem seznamu šteti vrednosti, ki manjkajo na drugem seznamu, lahko uporabite formulo, ki temelji na funkcijah COUNTIF in SUMPRODUCT.
V prikazanem primeru je formula v H6:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Kar vrne 1, ker se vrednost "Osborne" ne pojavi v B6: B11.
Pojasnilo
Funkcije COUNTIF preverjajo vrednosti v obsegu glede na merila. Pogosto je na voljo le eno merilo, v tem primeru pa več kot eno.
Na območju, smo dali COUNTIF named območje List1 (B6: B11), in meril, nudimo imenovano območje Seznam2 (F6: F8).
Ker damo COUNTIF več kot eno merilo, dobimo več rezultatov v matriki rezultatov, ki je videti takole: (2; 1; 0)
Šteti želimo le manjkajoče vrednosti, ki imajo po definiciji štetje nič, zato te vrednosti pretvorimo v TRUE in FALSE z stavkom "= 0", ki da: (FALSE; FALSE; TRUE)
Nato vrednosti TRUE FALSE prisilimo na 1s in 0s z dvojno negativnim operatorjem (-), ki ustvari: (0; 0; 1)
Na koncu s SUMPRODUCT seštevamo elemente v matriki in vrnemo skupno število manjkajočih vrednosti.
Alternativa z MATCH
Če imate raje bolj dobesedne formule, lahko uporabite spodnjo formulo, ki temelji na MATCH, ki dobesedno šteje vrednosti, ki "manjkajo" s funkcijo ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
