Jak korzystać z SUMIF w Arkuszach Google

Ten samouczek zawiera szczegółową demonstrację, jak używać funkcji SUMA.JEŻELI i SUMA.JEŻELI w Arkuszach Google wraz z formułami i przykładami.

SUMA.JEŻELI to jedna z funkcji matematycznych w Arkuszach Google, która służy do warunkowego sumowania komórek. Zasadniczo funkcja SUMA.JEŻELI wyszukuje określony warunek w zakresie komórek, a następnie dodaje wartości, które spełniają dany warunek.

Na przykład masz listę wydatków w arkuszach Google i chcesz tylko zsumować wydatki, które są powyżej określonej wartości maksymalnej. Lub masz listę pozycji zamówienia i odpowiadające im kwoty i chcesz znać tylko całkowitą kwotę zamówienia określonej pozycji. Tutaj przydaje się funkcja SUMIF.

SUMA.JEŻELI może służyć do sumowania wartości na podstawie warunku liczbowego, warunku tekstowego, warunku daty, symboli wieloznacznych, a także na podstawie pustych i niepustych komórek. Arkusze Google mają dwie funkcje do sumowania wartości na podstawie kryteriów: SUMA.JEŻELI i SUMA. Funkcja SUMIF sumuje liczby na podstawie jednego warunku, podczas gdy SUMIFS sumuje liczby na podstawie wielu warunków.

W tym samouczku wyjaśnimy, jak używać funkcji SUMIF i SUMIFS w Arkuszach Google do sumowania liczb spełniających określone warunki.

Funkcja SUMIF w Arkuszach Google – składnia i argumenty

Funkcja SUMA.JEŻELI to tylko połączenie funkcji SUMA i JEŻELI. Funkcja JEŻELI skanuje zakres komórek dla danego warunku, a następnie funkcja SUMA sumuje liczby odpowiadające komórkom spełniającym warunek.

Składnia funkcji SUMA.JEŻELI:

Składnia funkcji SUMA.JEŻELI w Arkuszach Google jest następująca:

=SUMA.JEŻELI(zakres; kryteria; [suma_zakres])

Argumenty:

zakres - Zakres komórek, w których szukamy komórek spełniających kryteria.

kryteria – Kryteria określające, które komórki należy dodać. Kryterium można oprzeć na liczbie, ciągu tekstowym, dacie, odwołaniu do komórki, wyrażeniu, operatorze logicznym, znaku wieloznacznym oraz innych funkcjach.

sum_zakres – Ten argument jest opcjonalny. Jest to zakres danych z wartościami do zsumowania, jeśli odpowiedni wpis zakresu spełnia warunek. Jeśli nie podasz tego argumentu, zamiast tego zostanie zsumowany „zakres”.

Zobaczmy teraz, jak używać funkcji SUMA.JEŻELI do sumowania wartości z różnymi kryteriami.

Funkcja SUMA.JEŻELI z kryteriami liczbowymi

Liczby spełniające określone kryteria można sumować w zakresie komórek, używając jednego z poniższych operatorów porównania do tworzenia kryteriów.

  • większe niż (>)
  • mniej niż (<)
  • większe lub równe (>=)
  • mniejszy lub równy (<=)
  • równy (=)
  • nie równa ()

Załóżmy, że masz następujący arkusz kalkulacyjny i interesuje Cię całkowita sprzedaż, która wynosi 1000 lub więcej.

Oto jak wprowadzić funkcję SUMA.JEŻELI:

Najpierw wybierz komórkę, w której chcesz wyświetlić wynik sumy (D3). Aby zsumować liczby w B2:B12, które są większe lub równe 1000, wpisz tę formułę i naciśnij „Enter”:

=SUMA.JEŻELI(B2:B12;">=1000";B2:B12)

W tej przykładowej formule argumenty zakres i suma_zakres (B2:B12) są takie same, ponieważ liczby sprzedaży i kryteria są stosowane do tego samego zakresu. Wprowadziliśmy liczbę przed operatorem porównania i umieściliśmy ją w cudzysłowie, ponieważ kryteria powinny być zawsze ujęte w podwójny cudzysłów, z wyjątkiem odwołania do komórki.

Formuła szukała liczb większych lub równych 1000, a następnie sumowała wszystkie dopasowane wartości i wyświetlała wynik w komórce D3.

Ponieważ argumenty zakres i suma_zakres są takie same, możesz osiągnąć ten sam wynik bez argumentów sum_zakres w formule, na przykład:

=SUMA.JEŻELI(B2:B12;">=1000")

Możesz też podać odwołanie do komórki (D2) zawierające liczbę zamiast kryteriów liczbowych i połączyć operator porównania z tym odwołaniem do komórki w argumencie kryteriów:

=SUMA.JEŻELI(B2:B12;">="&D2)

Jak widać operator porównania jest nadal wprowadzany w cudzysłowie, a operator i odwołanie do komórki są połączone znakiem ampersand (&). I nie musisz umieszczać odwołania do komórki w cudzysłowie.

Notatka: Odwołując się do komórki zawierającej kryteria, upewnij się, że w wartości w komórce nie pozostawiasz żadnych początkowych ani końcowych spacji. Jeśli Twoja wartość ma niepotrzebną spację przed lub po wartości w komórce, do której odwołuje się, formuła zwróci w rezultacie „0”.

Możesz również użyć innych operatorów logicznych w ten sam sposób, aby utworzyć warunki w argumencie kryteria. Na przykład, aby zsumować wartości mniejsze niż 500:

=SUMA.JEŻELI(B2:B12;"<500")

Suma, jeśli liczby są równe

Jeśli chcesz dodać liczby równe określonej liczbie, możesz wpisać tylko liczbę lub liczbę ze znakiem równości w argumencie kryterium.

Na przykład, aby zsumować odpowiednie kwoty sprzedaży (kolumna B) dla ilości (kolumna C), których wartość jest równa 20, wypróbuj dowolną z poniższych formuł:

=SUMA.JEŻELI(C2:C12;"=20";B2:B12)
=SUMA.JEŻELI(C2:C12;"20";B2:B12)
=SUMA.JEŻELI(C2:C12;E2;B2:B12)

Aby zsumować liczby w kolumnie B z ilością nie równą 20 w kolumnie C, wypróbuj tę formułę:

=SUMA.JEŻELI(C2:C12;"20";B2:B12)

Funkcja SUMA.JEŻELI z kryteriami tekstowymi

Jeśli chcesz zsumować liczby w zakresie komórek (kolumna lub wiersz) odpowiadającym komórkom zawierającym określony tekst, możesz po prostu dołączyć ten tekst lub komórkę zawierającą tekst w argumencie kryteria formuły SUMA.JEŻELI. Należy pamiętać, że ciąg tekstowy powinien być zawsze ujęty w cudzysłów (” „).

Na przykład, jeśli chcesz uzyskać całkowitą kwotę sprzedaży w regionie „Zachód”, możesz użyć poniższej formuły:

=SUMA.JEŻELI(C2:C13;"Zachód";B2:B13)

W tej formule funkcja SUMA.JEŻELI wyszukuje wartość „Zachód” w zakresie komórek C2:C13 i dodaje odpowiednią wartość sprzedaży w kolumnie B. Następnie wyświetla wynik w komórce E3.

Możesz także odwołać się do komórki zawierającej tekst zamiast używać tekstu w argumencie kryteria:

=SUMA.JEŻELI(C2:C12;E2;B2:B12)

Teraz zmierzmy łączne przychody ze wszystkich regionów z wyjątkiem „Zachodu”. W tym celu użyjemy operatora not equal to () w formule:

=SUMA.JEŻELI(C2:C12;""&E2;B2:B12)

SUMIF z symbolami wieloznacznymi

W powyższej metodzie funkcja SUMA.JEŻELI z kryteriami tekstowymi porównuje zakres z dokładnie określonym tekstem. Następnie sumuje liczby do dokładnego tekstu i ignoruje wszystkie inne liczby, w tym częściowo dopasowany ciąg tekstowy. Aby zsumować liczby z częściowymi pasującymi ciągami tekstowymi, musisz dostosować jeden z następujących symboli wieloznacznych w swoich kryteriach:

  • ? (znak zapytania) służy do dopasowania dowolnego pojedynczego znaku w dowolnym miejscu ciągu tekstowego.
  • * (gwiazdka) służy do wyszukiwania pasujących słów wraz z dowolną sekwencją znaków.
  • ~ (tylda) służy do dopasowywania tekstów ze znakiem zapytania (?) lub gwiazdką (*).

Ten przykładowy arkusz kalkulacyjny dla produktów i ich ilości, aby zsumować liczby za pomocą symboli wieloznacznych:

Gwiazdka (*) Symbol wieloznaczny

Na przykład, jeśli chcesz zsumować ilości wszystkich produktów Apple, użyj tego wzoru:

=SUMA.JEŻELI(A2:A14;"Jabłko*";B2:B14)

Ta formuła SUMIF wyszukuje wszystkie produkty ze słowem „Apple” na początku i dowolną liczbą znaków po nim (oznaczonych przez „*”). Po znalezieniu dopasowania podsumowuje Ilość liczby odpowiadające pasującym ciągom tekstowym.

W kryteriach można również użyć wielu symboli wieloznacznych. Możesz także wprowadzić znaki wieloznaczne z odwołaniami do komórek zamiast tekstu bezpośredniego.

Aby to zrobić, symbole wieloznaczne muszą być ujęte w podwójne cudzysłowy („”) i połączone z odwołaniami do komórek:

=SUMA.JEŻELI(A2:A14;"*"&D2&"*";B2:B14)

Ta formuła sumuje ilości wszystkich produktów, które mają w sobie słowo „Redmi”, bez względu na to, gdzie to słowo znajduje się w ciągu.

Znak zapytania (?) Symbol wieloznaczny

Możesz użyć symbolu wieloznacznego znaku zapytania (?), aby dopasować ciągi tekstowe do dowolnych pojedynczych znaków.

Na przykład, jeśli chcesz znaleźć ilości wszystkich wariantów Xiaomi Redmi 9, możesz skorzystać z tego wzoru:

=SUMA.JEŻELI(A2:A14;"Xiaomi Redmi 9?";B2:B14)

Powyższa formuła wyszukuje ciągi tekstowe ze słowem „Xiaomi Redmi 9”, po których następują pojedyncze znaki i sumuje odpowiednie Ilość liczby.

Tylda (~) Dzika karta

Jeśli chcesz dopasować rzeczywisty znak zapytania (?) lub gwiazdkę (*), wstaw znak tyldy (~) przed symbolem wieloznacznym w części warunku formuły.

Aby dodać ilości w kolumnie B z odpowiednim ciągiem, który ma na końcu znak gwiazdki, wprowadź poniższą formułę:

=SUMA.JEŻELI(A2:A14;"Samsung Galaxy V~*";B2:B14)

Aby dodać ilości w kolumnie B, które mają znak zapytania (?) w kolumnie A w tym samym wierszu, wypróbuj poniższą formułę:

=SUMA.JEŻELI(A2:A14;"~?";B2:B14)

Funkcja SUMA.JEŻELI z kryteriami daty

Funkcja SUMA.JEŻELI może również pomóc w warunkowym sumowaniu wartości na podstawie kryteriów dat — na przykład liczb odpowiadających określonej dacie lub przed datą lub po dacie. Możesz również użyć dowolnego operatora porównania z wartością daty, aby utworzyć kryteria dat do sumowania liczb.

Data musi być wpisana w formacie daty obsługiwanym przez arkusze Google, jako odwołanie do komórki zawierającej datę lub przy użyciu funkcji daty, takiej jak DATE() lub DZIŚ().

Użyjemy tego przykładowego arkusza kalkulacyjnego, aby pokazać, jak działa funkcja SUMA.JEŻELI z kryteriami dat:

Załóżmy, że chcesz zsumować kwoty sprzedaży, które miały miejsce w dniu lub przed (<=) 29 listopada 2019 r. w powyższym zestawie danych, możesz dodać te liczby sprzedaży za pomocą funkcji SUMA.JEŻELI na jeden z następujących sposobów:

=SUMA.JEŻELI(C2:C13;"<=29 listopada 2019 r.;B2:B13)

Powyższa formuła sprawdza każdą komórkę od C2 do C13 i dopasowuje tylko te komórki, które zawierają daty w dniu 29 listopada 2019 r. lub wcześniej (29.11.2019 r.). A następnie sumuje kwotę sprzedaży odpowiadającą pasującym komórkom z zakresu komórek B2:B13 i wyświetla wynik w komórkach E3.

Datę można podać w formule w dowolnym formacie rozpoznawanym przez Arkusze Google, np. „29 listopada 2019 r.”, „29 listopada 2019 r.” lub „29.11.2019 r.” itp. Zapamiętaj wartość daty, a operator musi zawsze być ujęty w podwójny cudzysłów.

Możesz również użyć funkcji DATE() w kryteriach zamiast bezpośredniej wartości daty:

=SUMA.JEŻELI(C2:C13;"<="&DATA(2019;11,29);B2:B13)

Możesz też użyć odwołania do komórki zamiast daty w części formuły dotyczącej kryteriów:

=SUMA.JEŻELI(C2:C13;"<="&E2;B2:B13)

Jeśli chcesz zsumować kwoty sprzedaży na podstawie dzisiejszej daty, możesz użyć funkcji DZIŚ() w argumencie kryteria.

Na przykład, aby zsumować wszystkie kwoty sprzedaży na dzień dzisiejszy, użyj następującej formuły:

=SUMA.JEŻELI(C2:C13;DZIŚ();B2:B13)

Funkcja SUMA.JEŻELI z pustymi lub niepustymi komórkami

Czasami może być konieczne zsumowanie liczb z zakresu komórek z pustymi lub niepustymi komórkami w tym samym wierszu. W takich przypadkach można użyć funkcji SUMA.JEŻELI, aby zsumować wartości na podstawie kryteriów, w których komórki są puste lub nie.

Suma, jeśli puste

W Arkuszach Google istnieją dwa kryteria wyszukiwania pustych komórek: „” lub „=”.

Na przykład, jeśli chcesz zsumować wszystkie kwoty sprzedaży zawierające ciągi o zerowej długości (wizualnie wyglądające na puste) w kolumnie C, użyj podwójnych cudzysłowów bez spacji w formule:

=SUMA.JEŻELI(C2:C13;"";B2:B13)

Aby zsumować wszystkie kwoty sprzedaży w kolumnie B z pełnymi pustymi komórkami w kolumnie C, podaj „=” jako kryterium:

=SUMA.JEŻELI(C2:C13;"=";B2:B13)

Suma, jeśli nie jest pusta:

Jeśli chcesz zsumować komórki zawierające dowolną wartość (nie pustą), możesz użyć „” jako kryterium w formule:

Na przykład, aby uzyskać łączną kwotę sprzedaży z dowolnymi datami, użyj tej formuły:

=SUMA.JEŻELI(C2:C13;"";B2:B13)

SUMIF na podstawie wielu kryteriów z logiką OR

Jak widzieliśmy do tej pory, funkcja SUMA.JEŻELI służy do sumowania liczb na podstawie tylko jednego kryterium, ale możliwe jest sumowanie wartości na podstawie wielu kryteriów za pomocą funkcji SUMA.JEŻELI w Arkuszach Google. Można to zrobić, łącząc więcej niż jedną funkcję SUMA.JEŻELI w jednej formule z logiką LUB.

Na przykład, jeśli chcesz zsumować wielkość sprzedaży w regionie „Zachód” lub „Południe” (logika OR) w określonym zakresie (B2:B13), użyj tego wzoru:

=SUMA.JEŻELI(C2:C13;"Zachód";B2:B13)+SUMA.JEŻELI(C2:C13;"Południe";B2:B13)

Ta formuła sumuje komórki, gdy co najmniej jeden z warunków ma wartość PRAWDA. Stąd jest znany jako „logika OR”. Będzie również sumować wartości, gdy wszystkie warunki zostaną spełnione.

Pierwsza część formuły sprawdza zakres C2:C13 pod kątem tekstu „Zachód” i sumuje wartości z zakresu B2:B13 po spełnieniu dopasowania. Druga część sprawdzania wartości tekstowej „Południe” w tym samym zakresie C2:C13, a następnie sumuje wartości z pasującym tekstem w tym samym zakresie sum_zakres B2:B13. Następnie obie sumy są sumowane i wyświetlane w komórce E3.

W przypadku spełnienia tylko jednego kryterium, zwróci tylko tę wartość sumy.

Możesz także użyć wielu kryteriów zamiast jednego lub dwóch. A jeśli używasz wielu kryteriów, lepiej użyć odwołania do komórki jako kryterium, zamiast wpisywać bezpośrednią wartość w formule.

=SUMA.JEŻELI(C2:C13;E2,B2:B13)+SUMA.JEŻELI(C2:C13;E3;B2:B13)+SUMA.JEŻELI(C2:C13;E4,B2:B13)

SUMA.JEŻELI z logiką OR dodaje wartości, gdy co najmniej jedno z określonych kryteriów jest spełnione. Jeśli jednak chcesz zsumować wartości tylko wtedy, gdy wszystkie określone warunki są spełnione, musisz użyć nowej funkcji siostrzanej SUMIFS().

Funkcja SUMIFS w Arkuszach Google (wiele kryteriów)

Gdy używasz funkcji SUMA.JEŻELI do sumowania wartości na podstawie wielu kryteriów, formuła może stać się zbyt długa i skomplikowana, co może prowadzić do błędów. Poza tym SUMA.JEŻELI pozwoli Ci sumować wartości tylko w jednym zakresie i gdy którykolwiek z warunków jest PRAWDZIWY. Tu właśnie wkracza funkcja SUMIFS.

Funkcja SUMI.Funkcja pomaga sumować wartości na podstawie wielu pasujących kryteriów w jednym lub kilku zakresach. I działa na logice AND, co oznacza, że ​​może sumować wartości tylko wtedy, gdy spełnione są wszystkie podane warunki. Nawet jeśli jeden warunek jest fałszywy, w wyniku zwróci „0”.

SUMIFS Składnia funkcji i argumenty

Składnia funkcji SUMIFS jest następująca:

=SUMA.WARUNKI(suma_zakres; kryteria_zakres1; kryterium1;[kryteria_zakres2...]; [kryterium2;...])

Gdzie,

  • sum_zakres – Zakres komórek zawierających wartości, które chcesz zsumować, gdy wszystkie warunki są spełnione.
  • kryteria_zakres1 – Jest to zakres komórek, w których sprawdzasz kryteria1.
  • kryteria1 – Jest to warunek, który należy sprawdzić pod kątem kryteriów_zakres1.
  • crzakres_iterii2, kryterium2, …– Dodatkowe zakresy i kryteria do oceny. Do formuły możesz dodać więcej zakresów i warunków.

Użyjemy zestawu danych na poniższym zrzucie ekranu, aby zademonstrować, jak funkcja SUMIFS działa z różnymi kryteriami.

SUMIFS z warunkami tekstowymi

Możesz sumować wartości na podstawie dwóch różnych kryteriów tekstowych w różnych zakresach. Załóżmy na przykład, że chcesz sprawdzić całkowitą kwotę sprzedaży dostarczonego przedmiotu namiotu. W tym celu użyj tej formuły:

=SUMA.WARUNKI(D2:D13;A2:A13;"Namiot";C2:C13;"Dostarczono")

W tej formule mamy dwa kryteria: „Namiot” i „Dostarczono”. Funkcja SUMA.WARUNKI sprawdza pozycję „Namiot” (kryteria1) w zakresie A2:A13 (zakres_kryteriów1) i sprawdza status „Dostarczono” (kryterium2) w zakresie C2:C13 (zakres_kryteriów2). Gdy oba warunki są spełnione, sumuje odpowiednią wartość z zakresu komórek D2:D13 (sum_zakres).

SUMIFS z kryteriami liczbowymi i operatorami logicznymi

Za pomocą operatorów warunkowych można tworzyć warunki z liczbami dla funkcji SUMA.

Aby znaleźć łączną sprzedaż ponad 5 ilości dowolnego przedmiotu w stanie Kalifornia (CA), użyj tego wzoru:

=SUMA.WARUNKI(E2:E13;D2:D13;">5;B2:B13;"CA")

Ta formuła ma dwa warunki: „>5” i „CA”.

Ta formuła sprawdza ilości (Ilość) większe niż 5 w zakresie D2:D13 i sprawdza stan „CA” w zakresie B2:B13. A gdy oba warunki są spełnione (co oznacza, że ​​są w tym samym wierszu), sumuje kwotę w E2:E13.

SUMIFS z kryteriami dat

Funkcja SUMIFS pozwala również sprawdzić wiele warunków w tym samym zakresie, a także w różnych zakresach.

Załóżmy, że chcesz sprawdzić całkowitą wielkość sprzedaży dostarczonych przedmiotów po 31.05.2021 r. i przed 10.06.2021 r., a następnie użyj tego wzoru:

=SUMA.WARUNKI(E2:E13;D2:D13;">"&G1,D2:D13;"<"&G2,C2:C13;G3)

Powyższa formuła ma trzy warunki: 31.05.2021, 10.05.2021 i Dostarczono. Zamiast używać bezpośredniej daty i wartości tekstowych, odwołaliśmy się do komórek zawierających te kryteria.

Formuła sprawdza daty po 31.05.2021 (G1) i daty przed 10.06.2021 (G2) w tym samym zakresie D2:D13 i sprawdza status „Dostarczono” między tymi dwiema datami. Następnie sumuje powiązaną kwotę w zakresie E2:E13.

SUMIFS z pustymi i niepustymi komórkami

Czasami możesz chcieć znaleźć sumę wartości, gdy odpowiednia komórka jest pusta lub nie. Aby to zrobić, możesz użyć jednego z trzech kryteriów, które omówiliśmy wcześniej: „=", „” i „”.

Na przykład, jeśli chcesz tylko zsumować ilość pozycji „Namiot”, dla których data dostawy nie została jeszcze potwierdzona (puste komórki), możesz użyć kryterium „=”:

=SUMA.WARUNKI(D2:D13;A2:A13;"Namiot";C2:C13;"=")

Formuła szuka pozycji „Namiot” (kryterium 1) w kolumnie A z odpowiednimi pustymi komórkami (kryteria 2) w kolumnie C, a następnie sumuje odpowiednią kwotę w kolumnie D. „=” reprezentuje całkowicie pustą komórkę.

Aby znaleźć sumę pozycji „Namiot”, dla których została potwierdzona data dostawy (nie puste komórki), użyj „” jako kryterium:

=SUMA.WARUNKI(D2:D13;A2:A13;"Namiot";C2:C13;"")

Po prostu zamieniliśmy „=” na „” w tej formule. Znajduje sumę elementów Namiot z niepustymi komórkami w kolumnie C.

SUMIFS z logiką OR

Ponieważ funkcja SUMIFS działa na logice AND, sumuje tylko wtedy, gdy spełnione są wszystkie warunki. Ale co, jeśli chcesz zsumować wartość na podstawie wielu kryteriów, gdy którekolwiek z kryteriów jest spełnione. Sztuką jest użycie wielu funkcji SUMIFS.

Na przykład, jeśli chcesz zsumować kwotę sprzedaży „Stojak na rower” LUB „Plecak”, gdy ich status to „Zamówione”, wypróbuj tę formułę:

=SUMIFS(D2:D13,A2:A13;"Stojak na rowery",C2:C13;"Zamówione") +SUMIFS(D2:D13;A2:A13;"Plecak";C2:C13;"Zamówione")

Pierwsza funkcja SUMIFS sprawdza dwa kryteria „Stojak na rower” i „Zamówiłem” i sumuje wartości kwot w kolumnie D. Następnie druga funkcja SUMIFS sprawdza dwa kryteria „Plecak” i „Zamówiono” i sumuje wartości kwot w kolumnie D. , obie sumy są sumowane i wyświetlane na F3. W prostych słowach ta formuła sumuje się, gdy zamawiany jest „Stelaż rowerowy” lub „Plecak”.

To wszystko, co musisz wiedzieć o funkcjach SUMIF i SUMIFS w Arkuszach Google.