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

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 tez 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 (większe od 1000) „>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ż wprowadzenie po prostu wartości 1000)
• „<=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 z jednek kolumny a podsumowuje wartości z innej kolumny.

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 jest 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 = 2200

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

SUMA.JEŻELI() pozwala stosować symbole wieloznaczne, dzięki którym kryteria dla tekstu mogą muszą pasować do określonego wzorca tekstowego.

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 jest wartość tekstowa z gwiazdką na końcu „Szkolenie*” co oznacza że chcemy aby tekst zaczynał się na szkolenie i po tym dowolna ilość znaków
• 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 ?” co oznacza że chcemy aby tekst zaczynał się tak „kurs „ i po tym konkretnie jeden dowolny znak.
• Sumowany zakres komórek (zaznaczony na czerwono) B2:B6;

Wynikiem jest 1900+100+220 dla trzech pierwszych pozycji.

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

Bardzo częstym problemem są zakresy o różnej liczbie wierszy. Co ważne, w takiej sytuacji Excel nie zawsze zgłasza błąd. Zamiast tego formuła zwraca wynik, który na pierwszy rzut oka może wyglądać poprawnie, ale w rzeczywistości jest niepełny.

Jeśli zakres, w którym sprawdzany jest warunek, jest krótszy niż zakres sumowania, Excel uwzględni tylko te wiersze, które znajdują się w obu zakresach jednocześnie.

W praktyce oznacza to, że część danych zostaje pominięta, a wynik jest zaniżony.

To jeden z bardziej podstępnych błędów, ponieważ Excel nie sygnalizuje problemu wprost. Dlatego przy pracy z SUMA.JEŻELI() zawsze warto upewnić się, że zakres warunku i zakres sumowania mają dokładnie taką samą liczbę wierszy.

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 takich sytuacjach konieczne jest sięgnięcie po inną funkcję Excela czyli SUMA.WARUNKÓW().

Aby łatwiej utrwalić wiedzę z tego artykułu, poniżej znajdziesz plik do pobrania, który zawiera:

• gotowe tabele z danymi,
• przykłady użycia funkcji SUMA.JEŻELI(),
• ćwiczenia do samodzielnego wykonania,
• zadania sprawdzające poprawne użycie warunków liczbowych i tekstowych,
• przykłady z operatorami oraz symbolami wieloznacznymi.

Plik pozwoli Ci samodzielnie przećwiczyć wszystkie sytuacje opisane w artykule i sprawdzić, jak funkcja działa w praktyce.