Formuła ogólna
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Podsumowanie
Aby policzyć pasujące wiersze z wieloma kryteriami LUB, możesz użyć formuły opartej na funkcji SUMPRODUCT. W pokazanym przykładzie formuła w F10 to:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Ta formuła zwraca liczbę wierszy, w których pierwsza kolumna to A lub B, a druga to X, Y lub Z.
Wyjaśnienie
Pracując od wewnątrz, każde kryterium jest stosowane z osobną konstrukcją ISNUMBER + MATCH. Aby wygenerować liczbę wierszy w pierwszej kolumnie, w której wartość to A lub B, używamy:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH generuje tablicę wyników, która wygląda następująco:
(1;2;#N/A;1;2;1;2)
a ISNUMBER konwertuje tę tablicę na tę tablicę:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Aby wygenerować liczbę wierszy w drugiej kolumnie, gdzie wartość to X, Y lub Z, używamy:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Następnie MATCH zwraca:
(1;2;3;3;#N/A;1;2)
a ISNUMBER konwertuje na:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Te dwie tablice są mnożone razem w SUMPRODUCT, który automatycznie konwertuje wartości TRUE FALSE na 1 i 0 w ramach operacji matematycznej.
Tak więc, aby zwizualizować, ostateczny wynik jest wyprowadzany w następujący sposób:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
Z odwołaniami do komórek
W powyższym przykładzie zastosowano zakodowane stałe tablicowe, ale możesz też użyć odwołań do komórek:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Więcej kryteriów
To podejście można „przeskalować w górę”, aby obsłużyć więcej kryteriów. Możesz zobaczyć przykład w tym wyzwaniu formuły.
