Formuła ogólna
=SUMPRODUCT(--(range1=criteria),range2)
Podsumowanie
Aby podsumować lub podsumować kolumny w oparciu o kryteria w sąsiednich kolumnach, możesz użyć formuły opartej na funkcji SUMPRODUCT. W przedstawionym przykładzie formuła w J5 to:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Wyjaśnienie
Zasadniczo ta formuła używa SUMPRODUCT do pomnożenia, a następnie sumy iloczynów dwóch tablic: tablica1 i tablica2 . Pierwsza tablica, tablica1 , działa jako „filtr”, zezwalając tylko na wartości spełniające kryteria.
Array1 używa zakresu, który zaczyna się w pierwszej kolumnie zawierającej wartości, które muszą spełniać kryteria. Te „wartości kryteriów” znajdują się w kolumnie po lewej stronie i bezpośrednio obok „wartości danych”.
Kryteria są stosowane jako prosty test, który tworzy tablicę wartości TRUE i FALSE:
--($B5:$H5=J$4)
Ten bit formuły „testuje” każdą wartość w pierwszej tablicy przy użyciu podanych kryteriów, a następnie używa podwójnego ujemnego (-) do przekształcenia wynikowych wartości PRAWDA i FAŁSZ na 1 i 0. Wynik wygląda następująco:
(1,0,0,0,1,0,1)
Należy zwrócić uwagę, że cyfry 1 odpowiadają kolumnom 1,5 i 7, które spełniają kryteria „A”.
Dla array2 wewnątrz SUMPRODUCT używamy zakresu, który jest „przesunięty” o jedną kolumnę w prawo. Ten zakres zaczyna się od pierwszej kolumny zawierającej wartości do zsumowania i kończy się ostatnią kolumną, która zawiera wartości do zsumowania.
Tak więc w przykładowej formule w J5 po wypełnieniu tablic otrzymujemy:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Ponieważ SUMPRODUCT jest zaprogramowany specjalnie do ignorowania błędów wynikających z mnożenia wartości tekstowych, ostateczna tablica wygląda następująco:
(1,0,0,0,1,0,1)
Jedynymi wartościami, które „przetrwają” mnożenie, są te, które odpowiadają 1s wewnątrz array1 . Możesz pomyśleć o logice w tablica1 „filtrującej” wartości w tablicy2 .
