Przy robieniu różnego rodzaju zestawień, często zachodzi potrzeba podsumowania tylko wartości dodatnich lub tylko ujemnych. Np. gdy tworzymy podsumowanie przychodów i rozchodów w firmie czy choćby w projekcie – często chcemy znać sumę tychże wpływów i wydatków. Jedną z metod jest stworzenie oddzielnych kolumn dla wpływów i oddzielną dla wydatków (tak się robi przy raportach kasowych), a potem posumowanie każdej z nich zwykłą funkcją  SUMA. To oczywiście działa i jest świetnym i prostym rozwiązaniem, jednak nie zawsze tak chcemy/możemy.

Pokażę wam dzisiaj również bardzo prostą metodę, która potrzebuje jednej kolumny z wartościami, gdzie wpływy będą dodatnie, a wydatki ujemne. Dane wyglądają t

Formatka

W komórce B14 chcę mieć sumę wpływów na konto, a w B15 – sumę wydatków.

Jeszcze zanim zacznę: zwróćcie uwagę, że na mojej liście wartości ujemne są zaznaczone na czerwono. Użyłam do tego zwykłego formatowania liczbowego (można to zrobić oczywiście formatowaniem warunkowym, jednak nie ma takiej potrzeby – można prościej). Zobaczcie:

Formatowanie liczbowe

Ok. Przejdźmy do formuły na wpływy i wydatki. Do obu obliczeń użyję funkcji SUMA.JEŻELI. Dla moich danych można ją napisać na co najmniej 2 sposoby. Do wyboru do koloru, choć ja wolę pierwszy (poniżej), ponieważ jest bardziej uniwersalny i niezależny od opisów.

Sposób 1 – kryteria oparte na liczbie

Aby obliczyć sumę wpływów, wpisz do komórki B14 taką formułę:

=SUMA.JEŻELI(B4:B11;">0")

A dla wydatków analogicznie (w B15):

=SUMA.JEŻELI(B4:B11;"<0")

I cała filozofia. Tak jak napisałam wcześniej: ta metoda jest bardziej uniwersalna, ponieważ bazuję na wartościach liczb, a nie na ich opisach (w kolumnie A). No i dzięki temu, że zarówno zakres w kolumnie B zarówno sumuję  jak i przeszukuję pod względem kryteriów – nie muszę wpisywać 3. argumentu.

Ale! Gdybyśmy mieli zarówno wpływy jak i wydatki wpisane jako wartości dodatnie – powyższa metoda już nie dałaby sobie rady…

Sposób 2 – kryteria oparte na tekście

Dla wpływów (B14):

=SUMA.JEŻELI($A$4:$A$11;A14;$B$4:$B$11)

Dla wydatków (B15):

=SUMA.JEŻELI($A$4:$A$11;A15;$B$4:$B$11)

Zobaczcie, że tutaj przeszukiwałam kolumnę A pod względem kryterium (pierwszy argument), a sumowałam kolumnę B (drugi argument). Dlatego też musiałąm wpisać wszystkie argumenty tej funkcji. Działa, tylko trochę więcej pisania. No i mamy klapę, gdy ktoś nam zrobi literówkę w opisie wpływ/wydatek. Natomiast jest to jedyna opcja, gdy zarówno wpływy jak i wydatki będą pisane jako wartości dodatnie. Poprzednia metoda by sobie z tym nie poradziła.

Jak widać – obie metody są ok, w zależności od danych, jakie mamy.

Tradycyjnie plik do pobrania poniżej:

I wersja wideo (dla pierwszej metody):