Generička formula
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Sažetak
Za brojanje vrijednosti na jednom popisu koje nedostaju na drugom popisu možete upotrijebiti formulu koja se temelji na funkcijama COUNTIF i SUMPRODUCT.
U prikazanom primjeru formula u H6 je:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Koji vraća 1 jer se vrijednost "Osborne" ne pojavljuje u B6: B11.
Obrazloženje
Funkcije COUNTIF provjeravaju vrijednosti u rasponu prema kriterijima. Često se navodi samo jedan kriterij, ali u ovom slučaju dajemo više kriterija.
Za opseg dajemo COUNTIF imenovani popis raspona1 (B6: B11), a za kriterije dajemo imenovani popis raspona2 (F6: F8).
Budući da dajemo COUNTIF više od jednog kriterija, dobivamo više rezultata u polju rezultata koje izgleda ovako: (2; 1; 0)
Želimo brojati samo one vrijednosti koje nedostaju, a koje po definiciji broje nula, pa pretvaramo ove vrijednosti u TRUE i FALSE pomoću izraza "= 0", koji daje: (FALSE; FALSE; TRUE)
Zatim prisiljavamo vrijednosti TRUE FALSE na 1s i 0s s dvostrukim negativnim operatorom (-), koji daje: (0; 0; 1)
Na kraju, koristimo SUMPRODUCT da zbrajamo stavke u polju i vraćamo ukupan broj nedostajućih vrijednosti.
Alternativa s MATCH
Ako više volite doslovnije formule, možete upotrijebiti formulu u nastavku, koja se temelji na MATCH, koja doslovno broji vrijednosti koje "nedostaju" pomoću funkcije ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
