Sumowanie według kryteriów za pomocą SUMIF

Zwykła funkcja SUM dodaje każdą liczbę z zakresu. Często jednak chcą Państwo dodać tylko niektóre z nich. Wyobraźmy sobie arkusz sprzedaży, w którym kolumna A zawiera region, a kolumna B kwotę. Nie chodzi o sumę wszystkich sprzedaży, lecz tylko o sumę dla regionu East.

Jest to suma warunkowa, a przeznaczoną do tego funkcją jest SUMIF. Dodaje ona liczby tylko wtedy, gdy spełniony jest pasujący warunek, automatycznie pomijając pozostałe.

Funkcja SUMIF przyjmuje kolejno trzy elementy:

• range komórki, które należy sprawdzić pod kątem warunku
• criteria wyszukiwany warunek
• sum_range komórki, które mają zostać zsumowane

Arkusz przeszukuje więc range, znajduje wiersze spełniające criteria, a następnie sumuje odpowiadające im komórki z sum_range.

Załóżmy, że nazwy regionów znajdują się w zakresie A2:A10, a kwoty sprzedaży w zakresie B2:B10. Aby zsumować wyłącznie sprzedaż w regionie East, należy sprawdzić kolumnę A pod kątem słowa East i dodać odpowiadające kwoty z kolumny B.

Arkusz sprawdza każdą komórkę z regionem. Gdy znajdzie w niej wartość East, pobiera kwotę z tego samego wiersza i dodaje ją do bieżącej sumy. Pozostałe regiony są pomijane.

Gdy warunkiem jest słowo, należy ująć je w podwójny cudzysłów: "East". Bez cudzysłowu arkusz uznałby, że East jest nazwą lub etykietą, i prawdopodobnie zwróciłby zero.

Wielkość liter nie ma znaczenia, więc "east" i "East" pasują do tych samych wierszy. Należy tylko upewnić się, że pisownia dokładnie odpowiada danym, a tekst nie zawiera przypadkowych spacji.

Ręczne wpisanie warunku jest w porządku, ale wygodniej umieścić go w komórce. Jeśli D1 zawiera słowo East, należy odwołać się do D1 zamiast wpisywać tekst.

Można teraz zmienić region w D1, a suma zaktualizuje się natychmiast. Dzięki temu formuła nadaje się do ponownego użycia, a jedna komórka staje się prostym przełącznikiem sterującym raportem.

Kryteria nie muszą być tekstem. Aby zsumować każdą kwotę równą dokładnie 500, wystarczy użyć tej liczby jako warunku.

Gdy testowana kolumna i kolumna sumowania są takie same, można nawet pominąć trzeci argument. W tym przypadku kolumna B jest jednocześnie sprawdzana i sumowana, więc dodane zostaną tylko wiersze, w których wartość B wynosi 500.

Funkcja SUMIF obsługuje nie tylko dokładne dopasowania. Aby sumować zakresy liczb, należy umieścić operator porównania w cudzysłowie:

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

Ta formuła sumuje każdą sprzedaż większą niż 100. Operator i liczba znajdują się wewnątrz jednego zestawu cudzysłowów.

Co zrobić, gdy próg znajduje się w komórce, na przykład D1 zawiera wartość 100, a Państwo chcą zsumować kwoty większe od tej wartości? Nie można użyć zapisu ">D1", ponieważ stałby się on dosłownym tekstem D1. Zamiast tego należy połączyć operator i komórkę za pomocą ampersandu.

Ampersand łączy tekst ">" z wartością znajdującą się w D1, tworząc w locie kryterium ">100".

Elementy range i sum_range powinny mieć tę samą wysokość i zaczynać się w tym samym wierszu. Jeśli nazwy regionów znajdują się w A2:A10, kwoty muszą znajdować się w B2:B10, a nie w B3:B11.

Gdy zakresy są przesunięte względem siebie, arkusz przyporządkowuje niewłaściwy region do niewłaściwej kwoty, a suma jest po cichu błędna. Należy zawsze sprawdzić, czy oba zakresy obejmują dokładnie te same wiersze.

Wyobraźmy sobie arkusz wydatków, w którym kategorie znajdują się w kolumnie A, a koszty w kolumnie B. Chcą Państwo utworzyć niewielką tabelę podsumowania pokazującą sumy dla kategorii Travel, Food i Office.

Należy umieścić etykietę każdej kategorii w komórkach D2, D3 i D4, a następnie napisać jedną funkcję SUMIF odwołującą się do komórki z etykietą. Po wypełnieniu nią kolejnych wierszy każdy wiersz zsumuje dane dla własnej kategorii. Jeden schemat formuły, trzy natychmiastowe sumy częściowe.

Proszę zauważyć, że w ostatnim przykładzie użyto A:A i B:B, czyli całych kolumn. Jest to wygodne, gdy wciąż dodawane są nowe wiersze, ponieważ nie trzeba wtedy rozszerzać zakresu.

Należy tylko upewnić się, że tekst wiersza nagłówka nie pasuje przypadkowo do kryterium, oraz zachować zgodność obu kolumn. W bardzo dużych arkuszach ograniczony zakres, na przykład A2:A1000, może być obliczany nieco szybciej.

Proszę sprawdzić, jak dobrze rozumieją Państwo kolejność argumentów funkcji SUMIF.

Wiedzą już Państwo, jak warunkowo sumować liczby za pomocą funkcji SUMIF. Najważniejsze informacje:

• Kolejność to range, criteria, sum_range.
• Kryteria tekstowe wymagają cudzysłowów, a dopasowanie nie uwzględnia wielkości liter.
• Operatory, takie jak ">100", znajdują się wewnątrz cudzysłowów; operator należy połączyć z komórką za pomocą &.
• Elementy range i sum_range muszą obejmować te same wiersze.

W następnej części poznają Państwo sposób zliczania pasujących wierszy za pomocą COUNTIF.