SUMA.JEŻELI() w Excelu, czyli jak sumować na podstawie warunku
W tym artykule pokażę Ci krok po kroku, jak działa SUMA.JEŻELI(), jak poprawnie zapisywać warunki liczbowe i tekstowe, jak korzystać z symboli wieloznacznych oraz na co uważać, gdy formuła nie daje oczekiwanych wyników. Na konkretnych przykładach zobaczysz, jak szybko wyciągać z danych dokładnie te liczby, których naprawdę potrzebujesz – bez ręcznego filtrowania i zbędnych kolumn pomocniczych.
A w dalszej części poradnika:
Do czego służy funkcja SUMA.JEŻELI()
Pracując w Excelu bardzo często trafiamy na sytuację, w której nie chcemy sumować wszystkich danych, a tylko te które spełniają określony warunek. I właśnie w takich momentach pojawia się funkcja SUMA.JEŻELI().
Funkcja ta analizuje dane krok po kroku, sprawdzając, które wartości spełniają określony warunek i tylko je uwzględnia w sumowaniu. Dzięki temu nie musisz filtrować tabeli ręcznie ani tworzyć dodatkowych kolumn pomocniczych.
Wyobraź sobie tabelę z danymi sprzedażowymi. Masz w niej wiele produktów, różne miesiące i różne kwoty. Interesuje Cię tylko jedna konkretna informacja, na przykład łączna wartość sprzedaży jednego produktu albo suma transakcji powyżej określonej kwoty. Zamiast analizować dane wiersz po wierszu, możesz skorzystać z funkcji, która automatycznie zsumuje tylko te wartości, które spełniają określony warunek. I dokładnie to robi funkcja SUMA.JEŻELI().
Warto też podkreślić, że SUMA.JEŻELI() działa na jednym warunku. Oznacza to, że Excel sprawdza tylko jedno kryterium naraz, np. nazwę produktu, miesiąc, status lub wartość liczbową większą albo mniejszą od określonej liczby. W większości podstawowych analiz to w zupełności wystarcza i dlatego jest to jedna z najczęściej wykorzystywanych funkcji w Excelu.
W angielskiej wersji Excela funkcja występuje pod nazwą SUMIF()
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.
Tutaj pobierz plik do tego poradnika
SUMA.WARUNKÓW() w Excelu – sumowanie z wieloma warunkami
SUMA.WARUNKÓW() to istotna funkcja analityczna w Excelu. Pozwala sumować wartości na podstawie dwóch lub więcej warunków jednocześnie, dzięki czemu idealnie sprawdza się w raportach sprzedaży, analizach finansowych i zestawieniach okresowych.
Jak zrobić wykres w Excelu – instrukcja krok po kroku
Wykresy w Excelu służą do szybkiego i czytelnego przedstawiania danych. Zamiast analizować kolumny liczb, możesz w kilka sekund zobaczyć zależności, porównać wartości albo sprawdzić, jak dane zmieniają się w czasie.
Data w Excelu – jak wstawiać, formatować i aktualizować daty
Z pozoru prosta rzecz, jak wpisanie daty w Excelu, potrafi przysporzyć sporo kłopotów, w szczególności dla osób mniej doświadczonych. Program nie rozpoznaje daty, sortowanie się psuje albo liczby same zmieniają
Skróty Klawiszowe Excel Przy Edycji i Wpisywaniu Danych. Najważniejsze Skróty Klawiszowe Excel.
15 najważniejszych skrótów klawiszowych w Excelu, które znacząco przyspieszą Twoją pracę przy edytowaniu i wprowadzaniu danych! 🚀 Niezależnie od tego, czy jesteś początkującym, czy zaawansowanym użytkownikiem Excela, te skróty pozwolą
Przydatne Triki w Excelu, Które Usprawnią Twoją Pracę!
Najlepsze triki w Excelu. Odkryj 5 niezwykłych trików w programie Excel, które mogą znacząco usprawnić Twoją pracę. Dowiedz się, jak wykorzystać niestandardowe skróty klawiaturowe, głęboko ukryć arkusze czy szybko znajdować
Niestandardowe Formatowanie Komórek Excel
Formatowanie komórek Excela w trybie niestandardowym jest sposobem wyświetlana danych w komórce, natomiast niekoniecznie jest wartością tej komórki.
Wartość komórki zawsze wyświetlona będzie w pasku formuły natomiast sposób jej
do osób, które chcą nauczyć się podstaw Excela za darmo w mniej niż godzinę.
Darmowy Kurs Excel dla początkujących