Jak używać funkcji SUMIF w programie Excel z wieloma kryteriami: 3 praktyczne przykłady

Funkcja SUMA.JEŻELI w programie Excel dodaje wartości spełniające jeden warunek. Aby zsumować dwa lub więcej warunków, które muszą być spełnione, użyj funkcji SUMA.JEŻELI ; aby zsumować wiersze spełniające jedną z dwóch wartości, dodaj oddzielne formuły SUMA.JEŻELI. Poniższe przykłady wykorzystują małą tabelę sprzedaży, aby pokazać wszystkie trzy wzorce i wyjaśnić, która funkcja do nich pasuje.

Utwórz spójną tabelę sprzedaży

Użyj tych przykładowych kolumn w arkuszu kalkulacyjnym. Nagłówek znajduje się w wierszu 1, a sześć rekordów sprzedaży w wierszach od 2 do 7. Poniższe formuły odnoszą się do tych konkretnych wierszy.

SUMIF i SUMIFS: co się zmienia?

Składnia jednego warunku jest następująca =SUMIF(range, criteria, [sum_range]). Zakres to kolumna sprawdzana przez Excela, kryteria to wartość lub reguła do dopasowania, a opcjonalny argument suma_zakres zawiera liczby do dodania. Na przykład, sprawdzanie nazw agentów w kolumnie A i dodawanie sprzedaży z kolumny E powoduje, że warunek i wartości znajdują się w oddzielnych zakresach.

W przypadku wielu warunków, które muszą być spełnione, Excel używa =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Należy zauważyć, że funkcja SUMA.WARUNKÓW umieszcza zakres sumy na pierwszym miejscu, a funkcja SUMA.JEŻELI na trzecim. Microsoft dokumentuje do 127 par zakres-kryterium dla funkcji SUMA.WARUNKÓW. Dopasowane zakresy powinny mieć ten sam rozmiar i kształt.

Przykład 1: Dodaj sprzedaż dla jednego agenta za pomocą funkcji SUMIF

Aby zsumować sprzedaż Charlesa we wszystkich produktach, miesiącach i regionach, wprowadź:

=SUMIF(A2:A7,"Charles",E2:E7)

Excel sprawdza, A2:A7czy w komórce znajduje się Karol, a następnie dodaje wartości w pasujących wierszach E2:E7. Wynik to 1250 USD (500 USD + 750 USD). Możesz zastąpić cudzysłów odwołaniem do komórki, na przykład =SUMIF(A2:A7,G2,E2:E7), jeśli komórka G2 zawiera Karola. Odwołanie do komórki ułatwia ponowne wykorzystanie wyniku przez innego agenta.

Przykład 2: Dodaj sprzedaż Alexa w styczniu za pomocą funkcji SUMIFS

W tym przypadku sprzedaż musi spełniać oba warunki: agentem jest Alex , a miesiąc to styczeń. Użyj funkcji SUMIFS, ponieważ każda para kryteriów musi być zgodna:

=SUMIFS(E2:E7,A2:A7,"Alex",C2:C7,"Jan")

Pierwszym argumentem jest zakres sprzedaży. Następna para sprawdza kolumnę Agent dla Alexa; ostatnia para sprawdza miesiąc dla stycznia. Uwzględniany jest tylko wiersz spełniający oba testy, więc odpowiedź to $420 . Aby umożliwić edycję kryteriów, umieść Alexa w komórce G2 i Jan w nagłówku H2, a następnie użyj =SUMIFS(E2:E7,A2:A7,G2,C2:C7,H2).

Przykład 3: Dodaj sprzedaż dla dowolnego z dwóch agentów

„Charles or Elliot” to warunek LUB . Jedną z prostych opcji jest dodanie dwóch wyników SUMA.JEŻELI:

=SUMIF(A2:A7,"Charles",E2:E7)+SUMIF(A2:A7,"Elliot",E2:E7)

Pierwsza funkcja SUMIF zwraca 1250 USD dla Karola; druga zwraca 1100 USD dla Elliota. Ich łączna suma wynosi 2350 USD . To podejście jest łatwe do odczytania i sprawdza się, gdy masz krótką listę nazwisk. Jeśli umieścisz nazwiska w komórkach G2 i H2, użyj =SUMIF(A2:A7,G2,E2:E7)+SUMIF(A2:A7,H2,E2:E7), aby móc zmienić listę bez przepisywania formuły. Jeśli kryteria się pokrywają, dodanie oddzielnych wyników może zliczyć ten sam wiersz więcej niż raz; te dwa różne nazwiska agentów nie pokrywają się.

Typowe błędy funkcji SUMIF i SUMIFS

• Zamiana argumentów: SUMA.JEŻELI zaczyna się od zakresu kryteriów; SUMA.WARUNKÓW zaczyna się od zakresu sumy. Sprawdź kolejność funkcji przed skopiowaniem formuły.
• Użycie funkcji SUMIF dla warunku AND: Dla agenta Alexa i miesiąca Jan razem, użyj funkcji SUMIFS. Oddzielne wyniki funkcji SUMIF dodają alternatywy (OR), a nie warunki jednoczesne.
• Nieprawidłowo wyrównane zakresy: W funkcji SUMIFS należy używać zakresów o pasujących wymiarach, takich jak E2:E7, A2:A7i C2:C7. Rozpoczęcie lub zakończenie jednego zakresu w innym wierszu może spowodować błąd lub nieprawidłową sumę.
• Pozostawienie kryteriów tekstowych bez cudzysłowów: Wpisz tekst, np. "Charles"w cudzysłowie, lub wskaż komórkę zawierającą ten tekst. W przypadku reguły porównania, wpisz go jako tekst, np ">500". .

Jeśli program Excel zwróci zero, sprawdź, czy kryteria dokładnie odpowiadają wartościom w arkuszu kalkulacyjnym i czy komórki „Sprzedaż” zawierają liczby, a nie tekst liczbowy. Aby uzyskać więcej informacji, zapoznaj się z dokumentacją funkcji SUMA.JEŻELI firmy Microsoft , dokumentacją funkcji SUMA.WARUNKÓW oraz przewodnikiem sumowania wartości z wieloma warunkami . W przypadku błędu zapoznaj się z instrukcjami rozwiązywania problemów z funkcjami SUMA.JEŻELI/SUMA.WARUNKÓW#VALUE! firmy Microsoft .

Użyj funkcji SUMA.JEŻELI do jednego testu, SUMA.JEŻELI, gdy wszystkie wymienione testy muszą być prawdziwe, oraz wielu obliczeń SUMA.JEŻELI, gdy celem jest uwzględnienie jednej lub drugiej wartości. Wybór funkcji zgodnie z tą logiką ułatwia weryfikację formuły w miarę rozrastania się arkusza.