Sumowanie według wielu warunków za pomocą SUMIFS

Funkcja SUMIF sumuje wartości spełniające pojedynczą regułę, na przykład całą sprzedaż z regionu East. W praktyce pytania zwykle są bardziej złożone: Jaka była sprzedaż w regionie East w styczniu? Są to dwa warunki jednocześnie.

W tym miejscu funkcja SUMIFS pokazuje swoje możliwości. Końcowa litera S oznacza, że można połączyć wiele kryteriów, a wiersz zostanie dodany do sumy tylko wtedy, gdy spełni każdy określony warunek.

W tej lekcji poznają Państwo kolejność argumentów, utworzą pierwszą sumę z wieloma kryteriami i unikną typowych błędów, które często sprawiają problemy.

Funkcja SUMIFS ma inną kolejność argumentów niż można oczekiwać na podstawie funkcji SUMIF. Wartości, które mają zostać zsumowane, podaje się najpierw, a następnie każdą parę warunków.

• sum_range — wartości do zsumowania
• criteria_range1, criteria1 — pierwszy test
• criteria_range2, criteria2 — drugi test

Można dodawać kolejne pary zakresu i kryterium, maksymalnie do 127 warunków. Schemat poniżej można odczytać na głos: zsumuj to, gdy to równa się temu, i gdy to równa się temu.

Załóżmy, że w tabeli kolumna A zawiera Region, kolumna B — Month, a kolumna C — Amount. Należy obliczyć łączną sprzedaż dla regionu East w styczniu.

Wartości do zsumowania znajdują się w zakresie C:C. Pierwszy warunek sprawdza zakres A:A pod kątem wartości "East", a drugi sprawdza zakres B:B pod kątem wartości "January". Wiersz zostanie uwzględniony tylko wtedy, gdy oba warunki są spełnione.

To najczęstszy błąd w funkcji SUMIFS. sum_range i każdy criteria_range muszą mieć identyczne wymiary — taką samą liczbę wierszy i kolumn.

Jeśli sum_range to C2:C100, a jeden z zakresów kryteriów to A2:A99, formuła zwróci błąd #VALUE!, ponieważ wiersze nie będą się pokrywać.

Najbezpieczniej używać tych samych wierszy początkowych i końcowych we wszystkich zakresach albo konsekwentnie stosować całe kolumny, takie jak A:A.

Wpisanie na stałe wartości "East" w cudzysłowie działa, ale elastyczny raport pozwala użytkownikowi wybrać region. Należy umieścić region w komórce F1, a miesiąc w komórce F2, a następnie użyć tych komórek jako kryteriów.

Zmiana wartości F1 lub F2 natychmiast przeliczy sumę. Należy zauważyć, że zwykłe odwołanie do komórki nie wymaga cudzysłowów — są one potrzebne tylko w przypadku dosłownego tekstu wpisanego w formule.

Kryteria nie ograniczają się do dokładnego tekstu. W przypadku liczb można używać operatorów porównania, umieszczając je w cudzysłowie.

• ">100" — większe niż 100
• "<=50" — mniejsze lub równe 50
• "<>0" — różne od zera

W tym przykładzie sumujemy kwoty w regionie East, ale tylko wiersze, w których sama kwota jest większa niż 100. Należy zauważyć, że sum_range i criteria_range mogą odnosić się do tej samej kolumny.

Co zrobić, jeśli próg znajduje się w komórce, a nie jest wpisany bezpośrednio? Nie można po prostu użyć zapisu ">F1" — spowodowałby on wyszukiwanie dosłownego tekstu F1. Zamiast tego należy połączyć operator z komórką za pomocą symbolu &.

Zapis ">"&F1 tworzy kryterium większe niż wartość znajdująca się w komórce F1. Ten sposób łączenia tekstu jest niezbędny w dynamicznych raportach sterowanych przez użytkownika.

Funkcję SUMIFS można łatwo rozszerzać. Dla każdej nowej reguły należy dodać kolejną parę zakresu i kryterium. Załóżmy, że kolumna D zawiera Salesperson. Można wtedy obliczyć sprzedaż z regionu East w styczniu, zrealizowaną przez "Maria".

Każdy warunek dodatkowo zawęża wynik. Ponieważ funkcja SUMIFS stosuje logikę AND, wiersz musi spełniać wszystkie trzy testy, aby zostać uwzględniony w sumie.

Kryteria tekstowe obsługują symbole wieloznaczne. Gwiazdka * dopasowuje dowolną liczbę znaków, a znak zapytania ? dopasowuje dokładnie jeden znak.

• "North*" dopasowuje North, Northeast i Northwest
• "*east*" dopasowuje wszystko, co zawiera fragment east

Spowoduje to zsumowanie kwot dla każdego regionu zaczynającego się od "North", co jest przydatne, gdy nazwy regionów mają wspólny prefiks.

Warto zapamiętać tę różnicę, ponieważ kolejność argumentów jest odwrócona:

• SUMIF: range, criteria, [sum_range] — zakres do sprawdzenia występuje pierwszy, a sum_range jest opcjonalny i znajduje się na końcu.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — sum_range zawsze występuje pierwszy.

Wskazówka: jeśli występuje więcej niż jeden warunek, należy od razu użyć funkcji SUMIFS. Wiele osób korzysta z SUMIFS nawet przy jednym kryterium, aby zachować jeden spójny schemat.

Gdy funkcja SUMIFS zwraca wartość 0, zwykle oznacza to, że żaden wiersz nie spełnił wszystkich warunków — a nie że formuła jest uszkodzona. Należy sprawdzić, czy w tekście nie ma ukrytych spacji, czy pisownia jest zgodna oraz czy liczba nie została zapisana jako tekst.

Szybka metoda diagnostyczna polega na usuwaniu po jednym warunku naraz. Jeśli suma pojawi się po usunięciu kryterium, to właśnie ono wykluczało wszystkie wiersze. Takie izolowanie warunków i testowanie sprawia, że debugowanie formuł z wieloma kryteriami jest szybkie.

Proszę sprawdzić swoją znajomość kolejności argumentów funkcji SUMIFS.

Można już sumować liczby spełniające kilka warunków jednocześnie:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range występuje pierwszy.
• Wszystkie zakresy muszą mieć ten sam rozmiar, w przeciwnym razie pojawi się błąd #VALUE!.
• Do porównywania z komórką służy zapis ">"&F1, a do częściowych dopasowań — * i ?.
• Warunki stosują logikę AND — wiersz musi spełniać je wszystkie.

Następnie: zliczanie wierszy według wielu warunków za pomocą COUNTIFS.