Formuła ogólna
=SUMPRODUCT(--((criteria1)+(criteria2)>0))
Podsumowanie
Aby policzyć wiersze przy użyciu wielu kryteriów w różnych kolumnach - z logiką LUB - możesz użyć funkcji SUMPRODUCT. W pokazanym przykładzie formuła w H7 to:
=SUMPRODUCT(--((C5:C11="blue")+(D5:D11="dog")>0))
Wyjaśnienie
W przedstawionym przykładzie chcemy policzyć wiersze, w których kolor jest „niebieski” LUB zwierzę to „pies”.
Funkcja SUMPRODUCT działa z tablicami natywnie, więc dla pierwszego kryterium używamy:
(C5:C11="blue")
Zwraca tablicę wartości TRUE FALSE w następujący sposób:
(TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
W przypadku drugiego kryterium używamy:
(D5:D11="dog")
Który zwraca:
(TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE)
Te dwie tablice są następnie łączone za pomocą dodawania (+), które automatycznie przekształca wartości TRUE FALSE na 1 i 0, aby utworzyć taką tablicę:
(2; 0; 1; 1; 1; 0; 1)
Nie możemy po prostu dodać tych wartości za pomocą SUMPRODUCT, ponieważ spowodowałoby to podwójne policzenie wierszy z „niebieskim” i „psem”. Tak więc używamy „> 0” razem z podwójną ujemną wartością (-), aby wymusić na wszystkich wartościach 1 lub zero:
--((2;0;1;1;1;0;1)>0)
Który przedstawia tę tablicę w SUMPRODUCT:
(1; 0; 1; 1; 1; 0; 1)
SUMPRODUCT zwraca następnie sumę wszystkich elementów.
Inne testy logiczne
Przykład pokazuje testy dla prostej równości, ale w razie potrzeby można zastąpić te instrukcje innymi testami logicznymi. Na przykład, aby policzyć wiersze, w których komórki w kolumnie A zawierają słowo „czerwone” LUB komórki w kolumnie B zawierają słowo „niebieski”, możesz użyć następującego wzoru:
=SUMPRODUCT(--(ISNUMBER(SEARCH("red",A1:A10))+ISNUMBER(SEARCH("blue",B1:B10))>0))
Zobacz więcej informacji o numerze ISNUMBER i funkcji SEARCH tutaj.
Więcej testów logicznych
Możesz również dodać więcej niż dwa testy wewnątrz funkcji SUMPRODUCT.
