Formula generică
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
rezumat
Pentru a număra valorile dintr-o listă care lipsesc dintr-o altă listă, puteți utiliza o formulă bazată pe funcțiile COUNTIF și SUMPRODUCT.
În exemplul prezentat, formula din H6 este:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Care returnează 1, deoarece valoarea „Osborne” nu apare în B6: B11.
Explicaţie
Funcțiile COUNTIF verifică valorile într-un interval în raport cu criteriile. Adesea, este furnizat un singur criteriu, dar în acest caz furnizăm mai multe criterii.
Pentru gama, vom da COUNTIF gama numit List1 (B6: B11), și criterii, oferim gama numit Lista2 (F6: F8).
Deoarece acordăm COUNTIF mai mult de un criteriu, obținem mai multe rezultate într-o matrice de rezultate care arată astfel: (2; 1; 0)
Vrem să numărăm numai valorile care lipsesc, care, prin definiție, au un număr de zero, deci convertim aceste valori în TRUE și FALSE cu instrucțiunea "= 0", care produce: (FALSE; FALSE; TRUE)
Apoi forțăm valorile TRUE FALSE la 1s și 0s cu operatorul dublu negativ (-), care produce: (0; 0; 1)
În cele din urmă, folosim SUMPRODUCT pentru a adăuga elementele din matrice și pentru a returna un număr total de valori lipsă.
Alternativă cu MATCH
Dacă preferați formule mai literal, puteți utiliza formula de mai jos, bazată pe MATCH, care numără literalmente valorile care „lipsesc” folosind funcția ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
