Formuła ogólna
=SUMPRODUCT((logical1)*(logical2))
Podsumowanie
Aby policzyć wiersze w tabeli spełniające wiele kryteriów, z których niektóre zależą od testów logicznych działających na poziomie wiersza, można użyć funkcji SUMPRODUCT.
Kontekst
Masz tabelę zawierającą wyniki meczów sportowych. Masz cztery kolumny: drużyna gospodarzy, drużyna gości, wynik gospodarzy, wynik gości. Dla danej drużyny chcesz liczyć tylko mecze (rzędy), w których drużyna wygrała u siebie. Łatwo jest policzyć mecze (rzędy), w których drużyna była gospodarzem, ale jak liczyć tylko zwycięstwa?
Jest to dobre użycie funkcji SUMPRODUCT, która natywnie może obsługiwać operacje tablicowe (pomyśl o operacjach zajmujących się zakresami).
W przedstawionym przykładzie formuła w komórce H5 to:
=SUMPRODUCT((B5:B10=G5)*(D5:D10>E5:E10))
Wyjaśnienie
Funkcja SUMPRODUCT jest zaprogramowana do obsługi tablic w sposób natywny, bez konieczności użycia klawisza Control Shift Enter. Jego domyślnym zachowaniem jest mnożenie razem odpowiednich elementów w jednej lub kilku tablicach, a następnie sumowanie iloczynów. Gdy otrzyma pojedynczą tablicę, zwraca sumę elementów w tablicy.
W tym przykładzie używamy dwóch wyrażeń logicznych w jednym argumencie tablicy. Moglibyśmy umieścić każde wyrażenie w osobnym argumencie, ale wtedy musielibyśmy wymusić logiczne wartości TRUE FALSE na jedynki i zera za pomocą innego operatora.
Używając operatora mnożenia, aby pomnożyć dwie tablice razem, program Excel automatycznie wymusi wartości logiczne na jedynki i zera.
Po obliczeniu dwóch wyrażeń logicznych formuła wygląda następująco:
=SUMPRODUCT(((FALSE;TRUE;FALSE;TRUE;FALSE;TRUE))*((TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)))
Po pomnożeniu dwóch tablic formuła wygląda następująco:
=SUMPRODUCT((0;1;0;0;0;1))
Gdy pozostała tylko jedna tablica, SUMPRODUCT po prostu dodaje elementy w tablicy i zwraca sumę.
