Formuła ogólna
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Podsumowanie
Aby liczyć według wielu kryteriów, w tym logiki NIE jednej z kilku rzeczy, możesz użyć funkcji SUMPRODUCT razem z funkcjami PODAJ.POZYCJĘ i ISNA.
W przedstawionym przykładzie formuła w G8 to:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Gdzie „płeć” to nazwany zakres C4: C12, a „grupa” to nazwany zakres D4: D12.
Uwaga: MATCH i ISNA umożliwiają łatwe skalowanie formuły w celu obsługi większej liczby wykluczeń, ponieważ można łatwo rozszerzyć zakres, aby uwzględnić dodatkowe wartości „NIE”.
Wyjaśnienie
Pierwsze wyrażenie wewnątrz SUMPRODUCTS sprawdza wartości w kolumnie C, Płeć, względem wartości w F4, „Mężczyzna”:
(gender=F4)
Wynikiem jest tablica wartości TRUE FALSE, jak ta:
(TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE)
Gdzie PRAWDA odpowiada „Mężczyzna”.
Drugie wyrażenie wewnątrz SUMPRODUCTS sprawdza wartości w kolumnie D, Grupa, względem wartości w G4: G5, „A” i „B”. Ten test jest obsługiwany przez MATCH i ISNA w następujący sposób:
ISNA(MATCH(group,G4:G5,0))
Funkcja PODAJ.POZYCJĘ służy do dopasowania każdej wartości z nazwanego zakresu „grupy” do wartości w G4: G5, „A” i „B”. Jeśli dopasowanie się powiedzie, PODAJ.POZYCJĘ zwraca liczbę. Jeśli MATCH nie powiedzie się, funkcja MATCH zwraca # N / A. Rezultatem jest tablica taka:
(1; 2; # nie dotyczy; 1; 2; # nie dotyczy; 1; 2; # nie dotyczy)
Ponieważ wartości # N / A odpowiadają wartościom „not A lub B”, ISNA służy do „odwracania” tablicy do:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Teraz PRAWDA odpowiada „nie A lub B”.
Wewnątrz SUMPRODUCT wyniki dwóch tablic są mnożone razem, co tworzy pojedynczą tablicę liczbową wewnątrz SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
SUMPRODUCT zwraca następnie sumę 2, reprezentującą „2 mężczyzn spoza grupy A lub B”.
