SUMA.JEŻELI w Excelu, czyli jak sumować na podstawie warunku

Sumowanie z warunkiem wygląda na prostą rzecz, dopóki wynik się nie zgadza. Formuła nie pokazuje żadnego błędu, tylko zero. Albo kwotę, której w tabeli nie ma. Albo liczy dobrze do chwili, gdy ktoś dopisze wiersz na dole arkusza. Każdy, kto raz sumował sprzedaż po jednym regionie, zna ten moment.

W tym poradniku przechodzimy SUMA.JEŻELI (SUMIF) po kolei: budowa i trzy argumenty, warunek liczbowy z operatorem, warunek tekstowy, a dalej symbole wieloznaczne na wypadek, gdy nazwy w kolumnie nie są identyczne. Na koniec trzy sytuacje, w których formuła wygląda dobrze, a wynik jest zły. Każdy krok na konkretnej tabeli, ze zrzutem z Excela. Jeśli szukasz zwykłej sumy, bez warunku, opisałem ją w osobnym poradniku: suma w Excelu.

Wszystko sprowadza się do jednej rzeczy, którą warto wiedzieć od początku: ta funkcja sprawdza warunek w jednej kolumnie, a sumuje wartości z drugiej, i to Ty wskazujesz, gdzie jest która. Pomylisz te dwa zakresy albo dasz im różną długość, a Excel nie powie ani słowa. Poda liczbę, która wygląda zupełnie wiarygodnie.

A w dalszej części poradnika:

1. Jak działa funkcja SUMA.JEŻELI
2. SUMA.JEŻELI - przykład
3. SUMA.JEŻELI z warunkiem tekstowym
4. Symbole wieloznaczne w funkcji SUMA.JEŻELI dla wartości tekstowych
5. Dlaczego SUMA.JEŻELI nie działa
6. Podsumowanie

Jak działa funkcja SUMA.JEŻELI

Funkcja składa się z trzech argumentów, sprawdza każdą komórkę w zakresie, porównując ją z podanym kryterium. Jeśli poszczególne komórki zakresu odpowiadają kryterium te komórki są sumowane. W funkcji możemy też wskazać trzeci argument suma_zakres aby zmienić zakres sumowanych komórek. Tak wygląda składnia funkcji:

=SUMA.JEŻELI(zakres; kryterium; [suma_zakres])

• Zakres to obszar komórek, w którym Excel ma sprawdzać warunek. To właśnie tutaj Excel sprawdza, które dane spełniają wskazany warunek.
• Kryterium z kolei definiuje, jakie warunki muszą zostać spełnione, aby dana wartość została uwzględniona. Może to być liczba, tekst, operator logiczny albo ich połączenie.
• Suma_zakres, jest opcjonalny. Jeśli go pominiesz, Excel zsumuje wartości bezpośrednio z zakresu, w którym sprawdzany jest warunek. W praktyce jednak bardzo często zakres warunku i zakres sumowania to dwie różne kolumny, np. warunek sprawdzany jest w kolumnie z nazwą produktu, a sumowane są wartości z kolumny z kwotami.

SUMA.JEŻELI - przykład

Najlepiej zobaczyć działanie funkcji SUMA.JEŻELI na konkretnych danych. Załóżmy, że w arkuszu znajduje się prosta tabela z listą zamówień oraz ich wartości. Celem jest obliczenie sumy tylko tych kwot, które są większe niż 1000.

W komórce D1 wpisujemy funkcję:

• Zakres danych jest to zakres komórek B2:B6
• Kryterium zapisujemy w cudzysłowie prostym: ">1000"

Excel analizuje każdą wartość z zakresu B2:B6 i sprawdza czy jest ona większa niż 1000. Do wyniku końcowego trafiają tylko te liczby, które spełniają ten warunek. Wynik to 1900+1700+1500 = 5100

Poniżej znajduje się ściągawka dla warunku kryterium porównywania wartości liczbowej:

• ">1000" - większe niż 1000
• ">=1000" - większe lub równe 1000
• "=1000" - równe tysiąc (tutaj zadziała też samo 1000, bez cudzysłowów)
• "<=1000" - mniejsze lub równe 1000
• "<1000" - mniejsze niż 1000
• "<>1000" - różne od 1000

W kolejnym przykładzie zobaczysz jak wykonać funkcję SUMA.JEŻELI, która sprawdza warunek w jednej kolumnie, a sumuje wartości z innej.

SUMA.JEŻELI z warunkiem tekstowym

Funkcja SUMA.JEŻELI bardzo często wykorzystywana jest nie tylko do pracy z liczbami, ale również z tekstem. Oznacza to, że możesz sumować wartości na podstawie nazwy kategorii, miesiąca albo statusu, który zapisany jest w innej kolumnie arkusza.

Załóżmy, że pracujesz z tabelą zawierającą informacje o zamówieniach. Oprócz kwoty każda pozycja ma przypisaną kategorię, np. szkolenie, konsultacja. Teraz naszym celem jest obliczenie łącznej wartości zamówień tylko dla kategorii „szkolenie”. W tym przykładzie warunek sprawdzany będzie w kolumnie „Kategoria”, natomiast sumowane będą wartości z kolumny „Kwota zamówienia”.

Zapisujemy funkcję SUMA.JEŻELI:

• Zakres danych (zaznaczony na niebiesko) C2:C6;
• Kryterium to wartość tekstowa "szkolenie"
• Sumowany zakres komórek (zaznaczony na czerwono) B2:B6;

Excel sprawdza każdą komórkę w zakresie C2:C6 i porównuje jej zawartość z tekstem "szkolenie". Jeśli w komórce jest „szkolenie” to do sumy dodaje komórkę z tego samego wiersza ale zakresu B2:B6. Wynikiem jest 1900 + 100 + 220 = 2220

🚀 Następny poziom

Sumujesz z jednym warunkiem. Prawdziwy raport potrzebuje kilku naraz

✓SUMA.WARUNKÓW i LICZ.WARUNKI - kilka warunków w jednej formule

✓Odwołania bezwzględne i F4, żeby formuła nie rozjechała się przy kopiowaniu

✓Tabele Excela (Ctrl + T), dzięki którym zakresy rosną razem z danymi

✓40 lekcji i 17 zadań z rozwiązaniami · gwarancja zwrotu 30 dni

Zobacz kurs

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy

Symbole wieloznaczne w funkcji SUMA.JEŻELI dla wartości tekstowych

SUMA.JEŻELI pozwala stosować symbole wieloznaczne, dzięki którym kryterium nie musi być dokładną nazwą - wystarczy, że tekst pasuje do wzorca.

Symbole wieloznaczne:

• * oznacza dowolną liczbę znaków,
• ? oznacza dowolny jeden znak.

Symbol wieloznaczny *

W poniższym przykładzie chcemy obliczyć sumę wartości wszystkich szkoleń a mamy tutaj dwa rodzaje „szkolenie online” oraz „szkolenie stacjonarne”

Zapisujemy funkcję SUMA.JEŻELI:

• Zakres danych (zaznaczony na niebiesko) C2:C6;
• Kryterium to wartość tekstowa z gwiazdką na końcu: "Szkolenie*", czyli tekst ma się zaczynać od słowa „Szkolenie”, a dalej może stać cokolwiek
• Sumowany zakres komórek (zaznaczony na czerwono) B2:B6;

Funkcja analizuje wszystkie komórki w zakresie C2:C6, dwie z tych komórek zaczynają się od słowa „Szkolenie” i kwoty dla tych komórek są sumowane. Wynikiem jest 100+1500 = 1600.

Symbol wieloznaczny ?

Podobnie działa znak zapytania. Jeśli nazwy różnią się tylko jednym znakiem, możesz użyć symbolu ?, aby Excel uwzględnił wszystkie pasujące warianty.

Zapisujemy funkcję SUMA.JEŻELI:

• Zakres danych (zaznaczony na niebiesko) C2:C6;
• Kryterium jest wartość tekstowa ze znakiem zapytania po spacji "Kurs ?", czyli tekst ma się zaczynać od „Kurs ”, a po spacji ma stać dokładnie jeden dowolny znak.
• Sumowany zakres komórek (zaznaczony na czerwono) B2:B6;

Do sumy wchodzą trzy pierwsze pozycje: 1900 + 100 + 220 = 2220.

Dlaczego SUMA.JEŻELI nie działa

Zdarza się, że funkcja wygląda poprawnie, a mimo to Excel zwraca wynik 0 albo zupełnie inną wartość, niż się spodziewasz. W większości przypadków problem nie leży w samej funkcji, ale w drobnych szczegółach związanych z zakresem danych lub zapisem kryterium. Poniżej znajdziesz najczęstsze przyczyny takich sytuacji.

Niepoprawny zakres sumy

Jednym z częstych błędów jest wskazanie niewłaściwego zakresu, z którego Excel ma pobierać wartości do zsumowania. Problem pojawia się wtedy, gdy warunek sprawdzany jest w jednej kolumnie, a suma dotyczy innej, ale zakres sumy nie odpowiada rzeczywistym danym.

Excel nie zgłasza w tym przypadku błędu składni, ale wynik nie ma sensu, ponieważ zakres sumy nie zawiera wartości liczbowych.

Różne rozmiary zakresów

To najbardziej podstępny z błędów, bo Excel nie zgłasza go w żaden sposób. Jeśli suma_zakres ma inny rozmiar niż zakres, SUMA.JEŻELI nie przerywa pracy. Bierze lewą górną komórkę zakresu sumowania i sama rozciąga go do rozmiaru zakresu warunku.

Weź formułę =SUMA.JEŻELI(B2:B7;"Zrealizowane";C4:C7). Zakres warunku ma sześć wierszy, zakres sumowania cztery. Excel nie sumuje więc zakresu C4:C7, tylko C4:C9, i dolicza kwoty z dwóch wierszy, które z Twoją tabelą nie mają już nic wspólnego.

Wynik wychodzi zawyżony albo zaniżony, zależnie od tego, co stoi pod tabelą, i wygląda przy tym zupełnie wiarygodnie. SUMA.WARUNKÓW (SUMIFS) zachowuje się tu odwrotnie: przy niezgodnych rozmiarach zakresów od razu zwraca #ARG!, więc o tyle łatwiej ją poprawić.

Nawyk, który to wyklucza: oba zakresy zaznaczaj jednym pociągnięciem w tych samych wierszach, a najlepiej zamień dane na tabelę Excela (Ctrl + T) i odwołuj się do nazw kolumn. Wtedy zakresy rosną razem z tabelą i nie da się ich rozjechać.

Błędnie zapisane kryterium

Trzecim częstym powodem problemów jest niepoprawny zapis kryterium. Może to być literówka, brak cudzysłowów w przypadku tekstu albo nieprawidłowe użycie operatora logicznego.

Excel interpretuje kryterium dosłownie, dlatego nawet niewielka niezgodność w zapisie powoduje, że dane nie zostają uwzględnione w obliczeniach.

Podsumowanie

Funkcja SUMA.JEŻELI to jedno z podstawowych, ale jednocześnie bardzo praktycznych narzędzi w Excelu. Pozwala szybko i wygodnie sumować dane na podstawie jednego, jasno określonego warunku, niezależnie od tego, czy dotyczy on wartości liczbowych czy tekstu.

Jeśli pracujesz z prostymi analizami, takimi jak suma sprzedaży dla jednej kategorii, suma kosztów powyżej określonej kwoty czy łączna wartość zamówień o danym statusie, SUMA.JEŻELI w zupełności wystarczy. Jest czytelna, łatwa do zapisania i bardzo dobrze sprawdza się w codziennej pracy z danymi.

Warto jednak pamiętać o jednym, kluczowym ograniczeniu: funkcja ta obsługuje tylko jeden warunek. Oznacza to, że w momencie, gdy chcesz jednocześnie uwzględnić więcej kryteriów, na przykład konkretną kategorię i miesiąc albo status i zakres kwot, SUMA.JEŻELI przestaje być wystarczająca.

W takiej sytuacji potrzebna jest funkcja SUMA.WARUNKÓW, która przyjmuje tyle warunków, ile potrzebujesz.

Zanim zamkniesz arkusz, sprawdź w nim trzy rzeczy, od których zależy poprawny wynik:

• czy zakres warunku i zakres sumowania obejmują te same wiersze,
• czy kryterium tekstowe stoi w cudzysłowie prostym, a nie w typograficznym wklejonym z Worda,
• czy liczby w kolumnie są liczbami, a nie tekstem (wyrównanie do lewej to pierwszy sygnał),
• czy w kryterium tekstowym nie ma spacji na końcu - Excel porównuje znak w znak.

Te cztery rzeczy odpowiadają za większość przypadków „formuła jest dobra, a wynik zły”.

Nowy kurs

Piszesz SUMA.JEŻELI co miesiąc od nowa? Tabela przestawna liczy to bez formuł

✓Suma po kategorii, miesiącu i statusie bez pisania formuł

✓Odświeżenie jednym kliknięciem, gdy dojdą nowe wiersze

✓Fragmentatory zamiast przeklejania kryterium do komórki

✓40 lekcji i 9 zestawów ćwiczeń · gwarancja zwrotu 30 dni

Zobacz kurs

Gwarancja zwrotu 30 dni · wszystkie lekcje dostępne od razu