Formuła ogólna
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Podsumowanie
Aby policzyć wartości z jednej listy, których brakuje na innej liście, możesz użyć formuły opartej na funkcjach COUNTIF i SUMPRODUCT.
W pokazanym przykładzie formuła w H6 to:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Co zwraca 1, ponieważ wartość „Osborne” nie pojawia się w B6: B11.
Wyjaśnienie
Funkcja LICZ.JEŻELI sprawdza wartości w zakresie pod kątem kryteriów. Często podaje się tylko jedno kryterium, ale w tym przypadku podajemy więcej niż jedno kryterium.
Dla zakresu podajemy LICZ.JEŻELI nazwany zakres lista1 (B6: B11), a dla kryteriów podajemy nazwany zakres lista2 (F6: F8).
Ponieważ dajemy LICZ.JEŻELI więcej niż jedno kryterium, otrzymujemy więcej niż jeden wynik w tablicy wyników, która wygląda następująco: (2; 1; 0)
Chcemy liczyć tylko brakujące wartości, które z definicji mają zero, więc konwertujemy te wartości na PRAWDA i FAŁSZ za pomocą instrukcji „= 0”, która daje: (FALSE; FALSE; TRUE)
Następnie wymuszamy wartości TRUE FALSE na 1 i 0 za pomocą operatora podwójnie ujemnego (-), który daje: (0; 0; 1)
Na koniec używamy SUMPRODUCT, aby zsumować elementy w tablicy i zwrócić całkowitą liczbę brakujących wartości.
Alternatywa z MATCH
Jeśli wolisz bardziej dosłowne formuły, możesz użyć poniższej formuły, opartej na PODAJ.POZYCJĘ, która dosłownie zlicza „brakujące” wartości za pomocą funkcji ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
