Jak używać sumy częściowej z sumą, w programie excel

Aby połączyć funkcje SUMA CZĘŚCIOWA i SUMA JEŚLI w programie Excel, możesz użyć następującej formuły:

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

Ta konkretna formuła pozwala zsumować wartości z zakresu C2:C11, gdzie odpowiadająca wartość z zakresu B2:B11 równa się „Zachowaj”, nawet po tym, jak ten zakres komórek został w jakiś sposób przefiltrowany.

Poniższy przykład pokazuje, jak zastosować tę formułę w praktyce.

Przykład: Jak używać sumy częściowej z sumą, jeśli w programie Excel

Załóżmy, że mamy następujący zbiór danych zawierający informacje o różnych koszykarzach:

Następnie przefiltrujmy dane, aby wyświetlić tylko wiersze, w których znajdują się gracze Konferencji Zachodniej .

Aby to zrobić, zaznacz zakres komórek A1:C11 . Następnie kliknij kartę Dane na górnej wstążce i kliknij przycisk Filtruj .

Następnie kliknij strzałkę rozwijaną obok opcji Konferencja i upewnij się, że zaznaczone jest tylko pole wyboru Zachód , a następnie kliknij OK :

Dane zostaną automatycznie przefiltrowane, aby wyświetlić tylko wiersze, w których kolumna Konferencja ma wartość Zachód :

Jeśli spróbujemy użyć funkcji SUMIF() do zsumowania wartości w kolumnie Punkty, gdzie wartość kolumny Pozycja jest równa „Ochrona”, tak naprawdę zwróci ona sumę punktów dla wierszy równych „Ochrona” w funkcji SUMIF() funkcjonować. oryginalny zbiór danych:

Zamiast tego musimy zastosować następującą formułę:

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

Poniższy zrzut ekranu pokazuje, jak zastosować tę formułę w praktyce:

Ta formuła zwraca poprawną sumę 85 .

Możemy potwierdzić, że jest to prawidłowe, ręcznie obliczając sumę wartości punktowych dla wierszy, w których kolumna Pozycja ma wartość „Straż”:

Suma punktów ochronnych: 12 + 28 + 30 + 15 = 85 .

Dodatkowe zasoby

Poniższe samouczki wyjaśniają, jak wykonywać inne typowe operacje w programie Excel:

Jak usunąć filtrowane wiersze w programie Excel
Jak liczyć filtrowane wiersze w programie Excel
Jak dodać filtrowane wiersze w programie Excel