Acest tutorial oferă o demonstrație detaliată a modului de utilizare a funcțiilor SUMIF și SUMIFS în Foi de calcul Google, cu formule și exemple.
SUMIF este una dintre funcțiile matematice din Google Sheets, care este folosită pentru a însuma în mod condiționat celulele. Practic, funcția SUMIF caută o condiție specifică într-un interval de celule și apoi adună valorile care îndeplinesc condiția dată.
De exemplu, aveți o listă de cheltuieli în foile Google și doriți doar să însumați cheltuielile care sunt peste o anumită valoare maximă. Sau aveți o listă de articole de comandă și sumele lor corespunzătoare și doriți doar să aflați valoarea totală a comenzii unui anumit articol. Acolo este utilă funcția SUMIF.
SUMIF poate fi folosit pentru a suma valorile bazate pe condiția numărului, condiția textului, condiția datei, caracterele metalice, precum și pe baza celulelor goale și negoale. Foi de calcul Google are două funcții de însumare a valorilor pe baza unor criterii: SUMIF și SUMIFS. Funcția SUMIF adună numere pe baza unei singure condiții, în timp ce SUMIFS adună numere pe baza mai multor condiții.
În acest tutorial, vom explica cum să utilizați funcțiile SUMIF și SUMIFS din Foi de calcul Google pentru a însuma numerele care îndeplinesc o anumită condiție(e).
Funcția SUMIF în Foi de calcul Google – Sintaxă și argumente
Funcția SUMIF este doar o combinație a funcției SUM și IF. Funcția IF scanează prin intervalul de celule pentru o anumită condiție, iar apoi funcția SUM însumează numerele corespunzătoare celulelor care îndeplinesc condiția.
Sintaxa funcției SUMIF:
Sintaxa funcției SUMIF în Foi de calcul Google este următoarea:
=SUMIF(interval, criterii, [interval_sumă])Argumente:
gamă - Gama de celule în care căutăm celulele care îndeplinesc criteriile.
criterii – Criteriile care determină ce celule trebuie adăugate. Puteți baza criteriul pe număr, șir de text, dată, referință de celulă, expresie, operator logic, caracter wildcard precum și alte funcții.
interval_sum – Acest argument este opțional. Este intervalul de date cu valori de însumat dacă intrarea corespunzătoare în interval se potrivește cu condiția. Dacă nu includeți acest argument, atunci „intervalul” este însumat.
Acum, să vedem cum să folosim funcția SUMIF pentru a însuma valori cu criterii diferite.
Funcția SUMIF cu criterii de număr
Puteți însuma numere care îndeplinesc anumite criterii într-un interval de celule, utilizând unul dintre următorii operatori de comparație pentru a crea criterii.
- mai mare decât (>)
- mai putin de (<)
- mai mare sau egal cu (>=)
- mai mic sau egal cu (<=)
- egal cu (=)
- nu este egal cu ()
Să presupunem că aveți următoarea foaie de calcul și sunteți interesat de vânzările totale care sunt 1000 sau mai mari.
Iată cum puteți introduce funcția SUMIF:
Mai întâi, selectați celula în care doriți să apară rezultatul sumei (D3). Pentru a rezuma numerele din B2:B12 care sunt mai mari sau egale cu 1000, tastați această formulă și apăsați „Enter”:
=SUMIF(B2:B12,">=1000",B2:B12)În acest exemplu de formulă, argumentele interval și sum_range (B2:B12) sunt aceleași, deoarece numerele de vânzări și criteriile sunt aplicate pe același interval. Și am introdus numărul înaintea operatorului de comparație și l-am încadrat între ghilimele, deoarece criteriile ar trebui să fie întotdeauna cuprinse între ghilimele duble, cu excepția unei referințe de celulă.
Formula a căutat numere care sunt mai mari sau egale cu 1000 și apoi a adăugat toate valorile potrivite și a arătat rezultatul în celula D3.
Deoarece argumentele interval și sum_range sunt aceleași, puteți obține același rezultat fără argumentele sum_range din formulă, astfel:
=SUMIF(B2:B12,">=1000")
Sau puteți furniza referința de celulă (D2) care conține numărul în loc de criteriile de număr și puteți alătura operatorului de comparație cu acea referință de celulă în argumentul criteriilor:
=SUMIF(B2:B12,">="&D2)După cum puteți vedea, operatorul de comparație este încă introdus între ghilimele duble, iar operatorul și referința de celulă sunt concatenate de un ampersand (&). Și nu trebuie să includeți referința la celulă între ghilimele.
Notă: Când vă referiți la celula care conține criterii, asigurați-vă că nu lăsați niciun spațiu de început sau de final în valoarea din celulă. Dacă valoarea dvs. are spațiu inutil înainte sau după valoarea din celula menționată, atunci formula va returna „0” ca rezultat.
De asemenea, puteți utiliza alți operatori logici în același mod pentru a face condiții în argumentul criteriilor. De exemplu, pentru a suma valori mai mici de 500:
=SUMIF(B2:B12,"<500")
Suma dacă numerele sunt egale cu
Dacă doriți să adăugați numere egale cu un anumit număr, puteți fie să introduceți doar numărul, fie să introduceți numărul cu semnul egal în argumentul criteriului.
De exemplu, pentru a suma sumele corespunzătoare vânzărilor (coloana B) pentru cantități (coloana C) ale căror valori sunt egale cu 20, încercați oricare dintre aceste formule:
=SUMIF(C2:C12;"=20",B2:B12)
=SUMIF(C2:C12;„20”, B2:B12)
=SUMIF(C2:C12;E2;B2:B12)
Pentru a suma numerele din coloana B cu o cantitate diferită de 20 din coloana C, încercați această formulă:
=SUMIF(C2:C12;„20”, B2:B12)
Funcția SUMIF cu criterii de text
Dacă doriți să adăugați numere într-un interval de celule (coloană sau rând) corespunzătoare celulelor care au un anumit text, puteți pur și simplu să includeți acel text sau celula care conține textul în argumentul de criterii al formulei SUMIF. Vă rugăm să rețineți că șirul de text ar trebui să fie întotdeauna cuprins între ghilimele duble (" ").
De exemplu, dacă doriți valoarea totală a vânzărilor în regiunea „Vest”, puteți utiliza formula de mai jos:
=SUMIF(C2:C13;„Vest”, B2:B13)În această formulă, funcția SUMIF caută valoarea „Vest” în intervalul de celule C2:C13 și adună valoarea vânzărilor corespunzătoare în coloana B. Apoi afișează rezultatul în celula E3.
De asemenea, puteți face referire la celula care conține text în loc să utilizați textul în argumentul criteriilor:
=SUMIF(C2:C12;E2;B2:B12)
Acum, să obținem veniturile totale ale tuturor regiunilor, cu excepția „Vest”. Pentru a face asta, vom folosi nu egal cu operatorul () din formula:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF cu WildCards
În metoda de mai sus, funcția SUMIF cu criterii de text verifică intervalul cu textul exact specificat. Apoi însumează numerele paralele la textul exact și ignoră toate celelalte numere, inclusiv șirul de text parțial potrivit. Pentru a însuma numerele cu șiruri de text parțial potrivite, trebuie să adaptați unul dintre următoarele caractere joker în criteriile dvs.:
?(semnul de întrebare) este folosit pentru a potrivi orice caracter, oriunde în șirul de text.*(asteriscul) este folosit pentru a găsi cuvinte care se potrivesc împreună cu orice secvență de caractere.~(tilde) este folosit pentru a potrivi textele cu un semn de întrebare (?) sau un caracter asterisc (*).
Vom folosi acest exemplu de foaie de calcul pentru produse și cantitățile acestora pentru a însuma numerele cu metacaractere:
Asterisc (*) Wildcard
De exemplu, dacă doriți să însumați cantitățile tuturor produselor Apple, utilizați această formulă:
=SUMIF(A2:A14;„Mere*”, B2:B14)Această formulă SUMIF găsește toate produsele cu cuvântul „Mere” la început și orice număr de caractere după el (notat cu „*”). Odată ce potrivirea este găsită, aceasta rezumă Cantitate numere corespunzătoare șirurilor de text potrivite.
De asemenea, este posibil să utilizați mai multe caractere joker în criterii. Și puteți introduce, de asemenea, caractere wildcard cu referințe de celule în loc de text direct.
Pentru a face acest lucru, metacaracterele trebuie să fie încadrate între ghilimele duble (" ") și concatenate cu referințele de celulă:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Această formulă adună cantitățile tuturor produselor care au cuvântul „Redmi” în ele, indiferent unde se află cuvântul în șir.
Semn de întrebare (?) Wildcard
Puteți utiliza semnul de întrebare (?) metacaracterul pentru a potrivi șirurile de text cu orice caracter.
De exemplu, dacă doriți să găsiți cantități din toate variantele Xiaomi Redmi 9, puteți utiliza această formulă:
=SUMIF(A2:A14;„Xiaomi Redmi 9?”, B2:B14)Formula de mai sus caută șiruri de text cu cuvântul „Xiaomi Redmi 9” urmat de orice caractere individuale și însumează valoarea corespunzătoare Cantitate numerele.
Tilde (~) Wildcard
Dacă doriți să potriviți un semn de întrebare (?) sau un caracter asterisc (*), introduceți caracterul tilde (~) înaintea caracterului joker în partea de condiție a formulei.
Pentru a adăuga cantitățile din coloana B cu șirul corespunzător care au semnul asterisc la sfârșit, introduceți formula de mai jos:
=SUMIF(A2:A14;„Samsung Galaxy V~*”, B2:B14)
Pentru a adăuga cantități în coloana B care au un semn de întrebare (?) în coloana A din același rând, încercați formula de mai jos:
=SUMIF(A2:A14,"~?",B2:B14)
Funcția SUMIF cu criterii de dată
Funcția SUMIF vă poate ajuta, de asemenea, să însumați în mod condiționat valorile pe baza criteriilor de dată – de exemplu, numere care corespund unei anumite date, sau înainte de o dată sau după o dată. De asemenea, puteți utiliza oricare dintre operatorii de comparație cu o valoare de dată pentru a crea criterii de dată pentru însumarea numerelor.
Data trebuie introdusă în formatul de dată acceptat pentru foi de calcul Google sau ca referință de celulă care conține o dată sau folosind o funcție de dată, cum ar fi DATE() sau TODAY().
Vom folosi acest exemplu de foaie de calcul pentru a vă arăta cum funcționează funcția SUMIF cu criterii de dată:
Să presupunem că doriți să însumați sumele vânzărilor care au avut loc la sau înainte de (<=) 29 noiembrie 2019 în setul de date de mai sus, puteți adăuga acele numere de vânzări folosind funcția SUMIF într-unul dintre următoarele moduri:
=SUMIF(C2:C13,"<=29 noiembrie 2019", B2:B13)Formula de mai sus verifică fiecare celulă de la C2 la C13 și se potrivește numai pentru acele celule care conțin date de la sau înainte de 29 noiembrie 2019 (29/11/2019). Și apoi însumează valoarea vânzărilor corespunzătoare acelor celule care se potrivesc din intervalul de celule B2:B13 și afișează rezultatul în celulele E3.
Data poate fi furnizată formulei în orice format care este recunoscut de Google Sheets, cum ar fi „29 noiembrie 2019”, „29 noiembrie 2019” sau „29/11/2019”, etc. Amintiți-vă valoarea datei și operatorul trebuie să să fie întotdeauna cuprinse între ghilimele duble.
De asemenea, puteți utiliza funcția DATE() în criterii, în loc de valea directă a datei:
=SUMIF(C2:C13,"<="&DATA(2019,11,29),B2:B13)
Sau puteți utiliza referința de celulă în loc de dată în partea cu criterii a formulei:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Dacă doriți să adăugați sumele vânzărilor împreună pe baza datei de astăzi, puteți utiliza funcția TODAY() în argumentul criteriilor.
De exemplu, pentru a suma toate sumele vânzărilor pentru data de astăzi, utilizați această formulă:
=SUMIF(C2:C13;AZI(),B2:B13)
Funcția SUMIF cu celule goale sau non-vide
Uneori, este posibil să fie nevoie să însumați numerele dintr-un interval de celule cu celule goale sau care nu sunt goale în același rând. În astfel de cazuri, puteți utiliza funcția SUMIF pentru a suma valorile pe baza criteriilor în care celulele sunt goale sau nu.
Suma dacă Blank
Există două criterii în Foi de calcul Google pentru a găsi celule goale: „” sau „=”.
De exemplu, dacă doriți să însumați toate sumele vânzărilor care conțin șiruri de lungime zero (vizual pare goală) în coloana C, utilizați ghilimele duble fără spațiu între ele în formulă:
=SUMIF(C2:C13,"",B2:B13)
Pentru a însuma toate sumele vânzărilor din coloana B cu celule goale complete din coloana C, includeți „=” drept criteriu:
=SUMIF(C2:C13,"=",B2:B13)
Suma dacă nu este goală:
Dacă doriți să însumați celulele care conțin orice valoare (nu goale), puteți utiliza „” ca criteriu în formulă:
De exemplu, pentru a obține valoarea totală a vânzărilor cu orice dată, utilizați această formulă:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Bazat pe criterii multiple cu logica SAU
După cum am văzut până acum, funcția SUMIF este proiectată să însumeze numere pe baza unui singur criteriu, dar este posibil să se însumeze valori pe baza mai multor criterii cu funcția SUMIF din Google Sheets. Se poate realiza prin alăturarea mai multor funcții SUMIF într-o singură formulă cu logica SAU.
De exemplu, dacă doriți să însumați suma vânzărilor în regiunea „Vest” sau regiunea „Sud” (logica SAU) în intervalul specificat (B2:B13), utilizați această formulă:
=SUMIF(C2:C13;„Vest”, B2:B13)+SUMIF(C2:C13;„Sud”, B2:B13)Această formulă însumează celulele atunci când cel puțin una dintre condiții este ADEVĂRATĂ. Prin urmare, este cunoscut sub numele de „logica SAU”. De asemenea, va însuma valorile atunci când toate condițiile sunt îndeplinite.
Prima parte a formulei verifică intervalul C2:C13 pentru textul „Vest” și însumează valorile din intervalul B2:B13 atunci când potrivirea este îndeplinită. Partea secundă a verificărilor pentru valoarea textului „Sud” în același interval C2:C13 și apoi însumează valorile cu textul potrivit în același interval de sum B2:B13. Apoi ambele sume sunt adăugate și afișate în celula E3.
În cazurile în care este îndeplinit un singur criteriu, va returna doar acea sumă.
De asemenea, puteți utiliza mai multe criterii în loc de doar unul sau două. Și dacă utilizați mai multe criterii, este mai bine să utilizați o referință de celulă ca criteriu în loc să scrieți valoarea directă în formulă.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF cu logica SAU adaugă valori atunci când cel puțin unul dintre criteriile specificate este îndeplinit, dar dacă doriți să însumați valori numai atunci când sunt îndeplinite toate condițiile specificate, trebuie să utilizați noua funcție SUMIFS() frate.
Funcția SUMIFS în Foi de calcul Google (criterii multiple)
Când utilizați funcția SUMIF pentru a însuma valori pe baza mai multor criterii, formula poate deveni prea lungă și complicată și sunteți predispus să faceți greșeli. În plus, SUMIF vă va permite să însumați valori doar pe un singur interval și atunci când oricare dintre condiții este TRUE. Aici intervine funcția SUMIFS.
Funcția SUMIFS vă ajută să însumați valori pe baza mai multor criterii de potrivire într-unul sau mai multe intervale. Și funcționează pe logica AND, ceea ce înseamnă că poate însuma valori numai atunci când sunt îndeplinite toate condițiile date. Chiar dacă o condiție este falsă, va returna „0” ca rezultat.
Sintaxa și argumentele funcției SUMIFS
Sintaxa funcției SUMIFS este următoarea:
=SUMIFS(interval_suma, interval_criterii1, criteriu1, [interval_criterii2, ...], [criteriul2, ...])Unde,
- interval_sumă - Intervalul de celule care conține valorile pe care doriți să le însumați atunci când sunt îndeplinite toate condițiile.
- criteria_range1 – Este intervalul de celule în care verificați criteriile1.
- criteriul 1 - Este condiția pe care trebuie să o verificați cu criteria_range1.
- criteria_range2, criteriu2, …– Intervalele și criteriile suplimentare de evaluat. Și puteți adăuga mai multe intervale și condiții la formulă.
Vom folosi setul de date din următoarea captură de ecran pentru a demonstra cum funcționează funcția SUMIFS cu diferite criterii.
SUMIFS cu Condiții Text
Puteți suma valorile pe baza a două criterii de text diferite în intervale diferite. De exemplu, să presupunem că doriți să aflați valoarea totală a vânzărilor a articolului de cort livrat. Pentru aceasta, utilizați această formulă:
=SUMIFS(D2:D13;A2:A13;„Cort”, C2:C13, „Livrat”)În această formulă, avem două criterii: „Cort” și „Livrat”. Funcția SUMIFS verifică articolul „Cort” (criterii1) în intervalul A2:A13 (interval_criterii1) și verifică starea „Livrat” (criterii2) în intervalul C2:C13 (interval_criterii2). Când ambele condiții sunt îndeplinite, atunci se însumează valoarea corespunzătoare din intervalul de celule D2:D13 (sum_range).
SUMIFS cu criterii numerice și operatori logici
Puteți utiliza operatori condiționali pentru a crea condiții cu numere pentru funcția SUMIFS.
Pentru a afla vânzările totale a mai mult de 5 cantități de orice articol în statul California (CA), utilizați această formulă:
=SUMIFS(E2:E13;D2:D13;">5",B2:B13;"CA")Această formulă are două condiții: „>5” și „CA”.
Această formulă verifică cantitățile (Cant.) mai mari de 5 în intervalul D2:D13 și verifică starea „CA” în intervalul B2:B13. Și când sunt îndeplinite ambele condiții (adică sunt în același rând), se însumează suma în E2:E13.
SUMIFS cu criterii de dată
Funcția SUMIFS vă permite, de asemenea, să verificați mai multe condiții din același interval, precum și diferite intervale.
Să presupunem că doriți să verificați valoarea totală a vânzărilor a articolelor livrate după 31/5/2021 și înainte de data de 10/6/2021, apoi utilizați această formulă:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Formula de mai sus are trei condiții: 31/5/2021, 10/5/2021 și Livrat. În loc să folosim date directe și valori de text, ne-am referit la celulele care conțin acele criterii.
Formula verifică datele după 31/5/2021 (G1) și date înainte de 10/6/2021 (G2) în același interval D2:D13 și verifică starea „Livrat” între aceste două date. Apoi, însumează suma aferentă în intervalul E2:E13.
SUMIFS cu celule goale și non-vide
Uneori, poate doriți să găsiți suma valorilor atunci când o celulă corespunzătoare este goală sau nu. Pentru a face acest lucru, puteți folosi unul dintre cele trei criterii pe care le-am discutat anterior: „=”, „”, și „”.
De exemplu, dacă doriți să însumați doar cantitatea de articole „Cort” pentru care data de livrare nu a fost încă confirmată (celule goale), puteți utiliza criteriile „=":
=SUMIFS(D2:D13;A2:A13;„Cort”, C2:C13,"=")Formula caută elementul „Cort” (criteriul 1) în coloana A cu celulele goale corespunzătoare (criteriile 2) în coloana C și apoi însumează suma corespunzătoare din coloana D. „=” reprezintă o celulă complet goală.
Pentru a găsi suma de articole „Cort” pentru care a fost confirmată data de livrare (nu celulele goale), utilizați „” ca criteriu:
=SUMIFS(D2:D13;A2:A13;„Cort”, C2:C13,””)Tocmai am schimbat „=” cu „” în această formulă. Găsește suma articolelor din cort cu celule care nu sunt goale în coloana C.
SUMIFS cu OR Logic
Deoarece funcția SUMIFS funcționează pe logica AND, se însumează numai atunci când sunt îndeplinite toate condițiile. Dar ce se întâmplă dacă doriți să însumați valoarea pe baza mai multor criterii atunci când oricare dintre criterii este îndeplinit. Trucul este să folosești mai multe funcții SUMIFS.
De exemplu, dacă doriți să adăugați suma vânzărilor fie pentru „Raport pentru biciclete” SAU pentru „Rucsac” atunci când starea lor este „Comandată”, încercați această formulă:
=SUMIFS(D2:D13,A2:A13,„Raport pentru biciclete”, C2:C13, „Comandat”) +SUMIFS(D2:D13,A2:A13, „Rucsac”, C2:C13, „Comandat”)Prima funcție SUMIFS verifică două criterii „Raport pentru biciclete” și „Comandat” și însumează valorile cantității din coloana D. Apoi, a doua SUMIFS verifică două criterii „Rucsac” și „Comandat” și însumează valorile cantității din coloana D. Și apoi , ambele sume sunt adunate și afișate pe F3. Cu cuvinte simple, această formulă însumează atunci când se comandă fie „Raport pentru biciclete”, fie „Rucsac”.
Acesta este tot ce trebuie să știți despre funcția SUMIF și SUMIFS în Foi de calcul Google.