Sumowanie warunkowe w Excelu

Autor: Marcin Sarna
Data: 11-02-2015 r.

Dodawanie komórek nie zawsze oznacza proste sumowanie określonego zakresu tabeli. Z tej porady dowiesz się, jak sumować komórki z określonego zakresu, które spełniają określone warunki.

Do sumowania warunkowego służy między innymi funkcja SUMA.WARUNKÓW, która pozwala na określenie więcej niż jednego warunku, jaki musi być spełniony przez każdą konkretną komórkę z zadanego zakresu, aby komórka ta została zsumowana.

 

Załóżmy, że chcemy zsumować tylko ceny tych produktów (kolumna A), które w sierpniu kosztowały powyżej 150 złotych (kolumna B), a we wrześniu poniżej 200 złotych (kolumna C). Dzięki funkcji SUMA.WARUNKÓW możemy więc zsumować liczby z zakresu A1:A10, ale nie wszystkie, a tylko te, którym odpowiadające liczby z zakresu B1:B10 są większe niż 150, a odpowiadające liczby z zakresu C1:C10 są mniejsze niż 200. Zastosowana formuła będzie wówczas miała postać:

=SUMA.WARUNKÓW(A1:A10;B1:B10;">150";C1:C10;"<200")

Rysunek 1. Ograniczyliśmy formułę do czterech pierwszych wierszy, aby łatwiej było zrozumieć przykład. Zsumowana została tylko wartość z komórki A3 (346), bo tylko dla niej spełnione były warunki, aby B3 było większe niż 150, a C4 mniejsze niż 200

Składnia funkcji SUMA.WARUNKÓW jest następująca:

SUMA.WARUNKÓW(suma; zakres1; kryteria1; zakres2; kryteria2;…)

W każdym przypadku użycia tej funkcji należy wskazać przynajmniej sumę oraz zakres1 i kryteria1. Podawanie kolejnych zakresów i kryteriów jest opcjonalne. Znaczenie tych wartości jest następujące:

  • suma – określenie, jaki zakres komórek ma być „przeglądany” i sumowany – o ile oczywiście zostaną spełnione dalsze warunki;

  • zakres1 – pierwszy zakres komórek, w którym będą sprawdzane warunki;

  • kryteria1 – warunki, które będą sprawdzane pod kątem ich spełnienia przez zakres1.

Poszczególne komórki z suma zostaną zsumowane tylko wówczas, gdy poszczególne komórki z zakres1 będą spełniały warunki z kryteria1, a w przypadku gdy zostaną podane dalsze zakresy i kryteria – muszą być spełnione wszystkie z nich jednocześnie.

Pierwsza komórka z suma będzie zsumowana tylko wówczas, jeśli dla pierwszej komórki z zakres1 zostanie spełniony warunek z kryteria1 i dla pierwszej komórki z zakres2 zostanie spełniony warunek z kryteria2 itd.

Z tego też powodu ważne jest, aby zakres1, zakres2 itd. zawierały dokładnie tę samą liczbę wierszy i kolumn co suma. W przeciwnym razie Excel nie będzie potrafił przyporządkować komórek z suma do zakresów i wyświetli błąd #ARG!.

Rysunek 2. Błąd #ARG! spowodowany małą zmianą w formule – z C4 na C3

Można w ten sposób sumować także komórki zawierające tylko wartości PRAWDA lub FAŁSZ. Wówczas PRAWDA „liczy się” za 1, a FAŁSZ za 0.

Przykłady

Załóżmy, że mamy tabelę taką jak na rysunku 3, która obrazuje sprzedaż silikonowych form. Pierwsza kolumna wskazuje ilość sprzedanych form, druga ich kolor, a trzecia numer jednego z dwóch handlowców zatrudnionych w firmie.

Rysunek 3. Przykładowa tabela

Jaką formułę zastosować, jeżeli potrzebujemy zsumować całkowitą liczbę form w kolorze czerwonym, sprzedanych przez sprzedawcę nr 2? Odpowiedź:

=SUMA.WARUNKÓW(A2:A8;B2:B8;"=C*";C2:C8;2)

W wyniku jej zastosowania otrzymamy sumę tych komórek A2:A8, dla których wartości odpowiednich komórek z kolumny B zaczynają się od litery „C”, a odpowiedne komórki z kolumny C mają wartość „2”. Warunek ten spełnia tylko komórka A4 o wartości 6.

Jeżeli chcemy policzyć wszystkie sprzedane formy, za wyjątkiem białych sprzedanych przez handlowca nr 1, użyjemy formuły:

=SUMA.WARUNKÓW(A1:A8;B1:B8;"<>biały";C1:C8;1)

Formuła wyświetli wynik 60, zsumuje bowiem zawartość komórek: A1 (10), A3 (30) i A7 (20).


Rysunek 4. Zastosowane w tekście formuły wraz z wynikami ich działań

Marcin Sarna

Zaloguj się, aby dodać komentarz

Nie masz konta? Zarejestruj się »

Zobacz także

Jak zatrzeć ślady po przeglądaniu Internetu?

pobierz

Wzór Polityki Bezpieczeństwa w ochronie danych osobowych

pobierz

Konfiguracja bezpieczeństwa. Windows 10

pobierz

10 sprytnych trików na szybkie obliczenia w Excelu

pobierz

Polecane artykuły

Array ( [docId] => 36549 )
Array ( [docId] => 36549 )