Funcții SUMIF & SUMIFS - Suma valori dacă - Excel și Foi de calcul Google

Acest tutorial arată cum să utilizați Excel SUMIF și SUMIFS Functiuni în Excel și Foi de calcul Google pentru a însuma datele care îndeplinesc anumite criterii.

Prezentare generală a funcției SUMIF

Puteți utiliza funcția SUMIF în Excel pentru a suma celulelor care conțin o anumită valoare, a sumelor de celule care sunt mai mari sau egale cu o valoare etc.

(Observați cum apar datele introduse de formulă)

Sintaxa și argumentele funcției SUMIF:

1	= SUMIF (interval, criterii, [sum_range])

gamă - Gama de celule pentru care doriți să aplicați criteriile.

criterii - Criteriile utilizate pentru a determina ce celule să adăugați.

gamă_sumă - [opțional] Celulele de adăugat împreună. Dacă sum_range este omis, celulele din interval sunt adăugate împreună.

Ce este funcția SUMIF?

Funcția SUMIF este una dintre funcțiile mai vechi utilizate în foile de calcul. Este folosit pentru a scana printr-o gamă de celule verificând un anumit criteriu și apoi adăugând valori într-un interval care corespunde acelor valori. Funcția SUMIF originală a fost limitată la un singur criteriu. După 2007, a fost creată funcția SUMIFS care permite o multitudine de criterii. Cea mai mare parte a utilizării generale rămâne aceeași între cele două, dar există unele diferențe critice în sintaxă pe care le vom discuta pe parcursul acestui articol.

Dacă nu ați făcut-o deja, puteți examina o mare parte din structura și exemplele similare din articolul COUNTIFS.

Exemplu de bază

Să luăm în considerare această listă a vânzărilor înregistrate și dorim să aflăm venitul total.

Deoarece am avut o cheltuială, valoarea negativă, nu putem face doar o sumă de bază. În schimb, vrem să însumăm doar valorile care sunt mai mari de 0. „Mai mare decât 0” este ceea ce va fi criteriul nostru într-o funcție SUMIF. Formula noastră pentru a afirma acest lucru este

1	= SUMIF (A2: A7, "> 0")

Exemplu cu două coloane

În timp ce funcția SUMIF originală a fost concepută pentru a vă permite să aplicați un criteriu pentru gama de numere pe care doriți să le însumați, de cele mai multe ori va trebui să aplicați unul sau mai multe criterii altor coloane. Să luăm în considerare acest tabel:

Acum, dacă folosim funcția SUMIF originală pentru a afla câte Banane avem (listate în celula D1), va trebui să oferim intervalul pe care îl dorim sumă ca ultim argument, și așa ar fi formula noastră

1	= SUMIF (A2: A7, D1, B2: B7)

Cu toate acestea, când programatorii au realizat în cele din urmă că utilizatorii doreau să dea mai multe criterii, a fost creată funcția SUMIFS. Pentru a crea o structură care ar funcționa pentru orice număr de criterii, SUMIFS necesită ca intervalul sumelor să fie listat mai întâi. În exemplul nostru, aceasta înseamnă că formula trebuie să fie

1	= SUME (B2: B7, A2: A7, D1)

NOTĂ: Aceste două formule obțin același rezultat și pot arăta asemănător, deci acordați o atenție deosebită funcției utilizate pentru a vă asigura că listați toate argumentele în ordinea corectă.

Lucrul cu date, criterii multiple

Când lucrați cu date într-o foaie de calcul, deși este posibilă introducerea datei direct în formulă, este recomandată să aveți data într-o celulă, astfel încât să puteți face referire doar la celulă într-o formulă. De exemplu, acest lucru ajută computerul să știe că doriți să utilizați data 27.05.2020 și nu numărul 5 împărțit la 27 împărțit la 2022.

Să ne uităm la următorul nostru tabel care înregistrează numărul de vizitatori pe un site la fiecare două săptămâni.

Putem specifica punctele de început și de sfârșit ale intervalului pe care dorim să îl privim în D2 și E2. Formula noastră de a însuma numărul de vizitatori din această gamă ar putea fi:

1	= SUME (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)

Rețineți cum am reușit să concatenăm comparațiile dintre „=” și referințele celulei pentru a crea criteriile. De asemenea, chiar dacă ambele criterii erau aplicate aceluiași interval de celule (A2: A7), trebuie să scrieți intervalul de două ori, o dată pentru fiecare criteriu.

Coloane multiple

Când utilizați mai multe criterii, le puteți aplica aceluiași interval ca și în exemplul anterior sau le puteți aplica unor intervale diferite. Să combinăm datele noastre eșantion în acest tabel:

Am configurat câteva celule pentru ca utilizatorul să introducă ceea ce dorește să caute în celulele E2 până la G2. Avem astfel nevoie de o formulă care să adune numărul total de mere culese în februarie. Formula noastră arată astfel:

1	= SUME (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)

SUMIFE cu logică de tip SAU

Până în prezent, exemplele pe care le-am folosit au fost toate bazate pe comparație bazată pe ȘI, în care căutăm rânduri care să îndeplinească toate criteriile noastre. Acum, vom lua în considerare cazul atunci când doriți să căutați posibilitatea ca un rând să îndeplinească unul sau alt criteriu.

Să vedem această listă de vânzări:

Am dori să adunăm vânzările totale atât pentru Adam, cât și pentru Bob. Pentru a face acest lucru, aveți câteva opțiuni. Cel mai simplu este să adăugați două SUMIFS împreună, astfel:

1	= SUMIFE (B2: B7, A2: A7, „Adam”) + SUMIFE (B2: B7, A2: A7, „Bob”)

Aici am primit computerul să ne calculeze scorurile individuale și apoi le adăugăm împreună.

Următoarea noastră opțiune este bună atunci când aveți mai multe intervale de criterii, astfel încât să nu doriți să fie necesar să rescrieți întreaga formulă în mod repetat. În formula anterioară, am spus manual computerului să adauge două SUMIFE diferite împreună. Cu toate acestea, puteți face acest lucru scriind criteriile într-o matrice, astfel:

1	= SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))

Uită-te la modul în care matricea este construită în parantezele crețate. Când computerul evaluează această formulă, va ști că vrem să calculăm o funcție SUMIFS pentru fiecare element din matricea noastră, creând astfel o matrice de numere. Funcția SUM exterioară va lua apoi acea matrice de numere și o va transforma într-un singur număr. Trecând prin evaluarea formulei, ar arăta astfel:

123	= SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871

Obținem același rezultat, dar am reușit să scriem formula puțin mai succint.

Tratarea golurilor

Uneori, setul de date va avea celule goale pe care trebuie să le găsiți sau să le evitați. Configurarea criteriilor pentru acestea poate fi puțin dificilă, așa că să analizăm un alt exemplu.

Rețineți că celula A3 este cu adevărat necompletată, în timp ce celula A5 are o formulă care returnează un șir de lungime zero de „”. Dacă vrem să găsim suma totală a cu adevărat celule goale, am folosi un criteriu „=”, iar formula noastră ar arăta astfel:

1	= SUME (B2: B7, A2: A7, "=")

Pe de altă parte, dacă dorim să obținem suma pentru toate celulele care arată vizual necompletate, vom schimba criteriile pentru a fi „”, iar formula arată ca

1	= SUME (B2: B7, A2: A7, "")

Să o răsfoim: ce se întâmplă dacă doriți să găsiți suma celulelor care nu sunt goale? Din păcate, designul actual nu vă permite să evitați șirul de lungime zero. Puteți utiliza un criteriu de „”, dar după cum puteți vedea în exemplu, acesta include în continuare valoarea din rândul 5.

1	= SUME (B2: B7, A2: A7, "")

Dacă nu trebuie să numărați celule care conțin șiruri de lungime zero, vă recomandăm să utilizați funcția LEN într-un SUMPRODUCT

SUMIF în Foi de calcul Google

Funcția SUMIF funcționează exact la fel în Foi de calcul Google ca în Excel: