Acest tutorial explică diferența dintre funcțiile SUMIF și SUMIFS în ceea ce privește sintaxa și și oferă o serie de exemple de formule pentru a suma valorilor cu mai multe criterii ȘI / SAU în Excel 2019, 2016, 2013, 2010, 2007, 2003 și mai mici.
După cum știe toată lumea, Microsoft Excel oferă o matrice de funcții pentru a efectua diverse calcule cu date. Cu câteva articole în urmă, am explorat COUNTIF și COUNTIFS, care sunt concepute pentru numărarea celulelor pe baza unei singure condiții și, respectiv, a mai multor condiții. Săptămâna trecută am tratat Excel SUMIF care adaugă valori care îndeplinesc criteriile specificate. Acum este timpul să treceți peste versiunea plurală a SUMIF – Excel SUMIFS care permite însumarea valorilor după mai multe criterii.
Cei care sunt familiarizați cu funcția SUMIF s-ar putea crede că convertirea acesteia în SUMIFS necesită doar un plus „S” și câteva criterii suplimentare. Acest lucru ar părea destul de logic… dar „logic” nu este întotdeauna cazul atunci când avem de-a face cu Microsoft:)
- Funcția SUMIF – sintaxă
- Funcția SUMIFS – sintaxă & utilizare
- Cum se utilizează SUMIFS în Excel (ȘI logică)
- Excel SUMIF cu mai multe criterii (OR logică)
- Excel SUMIFS cu mai multe condiții OR
- Utilizarea Excel SUM în formule matrice
Funcția SUMIF Excel – sintaxă & utilizare
Funcția SUMIF este utilizată pentru a suma condițională a valorilor pe baza unui singur criteriu. Am discutat în detaliu sintaxa SUMIF în articolul anterior, așa că permiteți-mi să vă ofer doar un rezumat rapid acum.
-
range– intervalul de celule care trebuie evaluat după criteriile dvs., obligatoriu. -
criteria– condiția care trebuie îndeplinită, obligatoriu. -
sum_range– celulele de sumat dacă condiția este îndeplinită, opțional.
După cum vedeți, sintaxa funcției Excel SUMIF permite o singură condiție. Și totuși, spunem că Excel SUMIF poate fi utilizat pentru a suma valorilor cu mai multe criterii. Cum poate fi asta? Prin adăugarea rezultatelor mai multor funcții SUMIF și prin utilizarea formulelor SUMIF cu criterii de matrice, după cum se demonstrează în exemplele următoare.
Funcția SUMIFS Excel – sintaxă &
Utilizați SUMIFS în Excel pentru a găsi o sumă condiționată de valori pe baza mai multor criterii. Funcția SUMIFS a fost introdusă în Excel 2007, astfel încât să o puteți utiliza în toate versiunile moderne ale Excel 2019, 2016, 2013, 2010 și 2007.
Comparativ cu SUMIF, sintaxa SUMIFS este puțin mai complexă :
Primele 3 argumente sunt obligatorii, intervalele suplimentare și criteriile asociate acestora sunt opționale.
-
sum_range– una sau mai multe celule de sumat, obligatorii. Aceasta poate fi o singură celulă, o gamă de celule sau un interval numit. Sunt însumate numai celulele cu numere; gol și valorile textului sunt ignorate. -
criteria_range1– primul interval care trebuie evaluat de criteriile asociate, obligatoriu. -
criteria1– prima condiție care trebuie îndeplinită, necesară. Puteți furniza criteriile sub formă de număr, expresie logică, referință de celulă, text sau altă funcție Excel. De exemplu, puteți utiliza criterii precum 10, „> = 10”, A1, „cireșe” sau TODAY (). -
criteria_range2, criteria2, …– acestea sunt intervale suplimentare și criterii asociate acestora, opționale. Puteți utiliza până la 127 de perechi de intervale / criterii în formulele SUMIFS.
Și acum, să „ Aruncați o privire la formula Excel SUMIFS cu două condiții. Să presupunem că aveți un tabel care prezintă loturile de fructe de la diferiți furnizori. Aveți numele fructelor în coloana A, numele furnizorilor în coloana B și cantitatea în coloana C. Ceea ce doriți este să aflați o sumă de sume referitoare la un anumit fruct și furnizor, de exemplu toate merele furnizate de Pete.
Când înveți ceva nou, este întotdeauna o idee bună să începi cu lucruri simple. Deci, pentru început, să definim toate argumentele pentru formula noastră SUMIFS:
- sum_range – C2: C9
- criteria_range1 – A2: A9
- criteriu1 – „mere”
- criteriu_interval2 – B2: B9
- criteriu2 – „Pete”
Acum asamblați parametrii de mai sus și va primi următoarea formulă SUMIFS:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Pentru a rafina formula mai departe, puteți înlocui criteriile de text „mere” și „Pete” cu referințe de celulă.În acest caz, nu va trebui să schimbați formula pentru a calcula cantitatea de alte fructe de la un furnizor diferit:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Utilizarea SUMIFS și SUMIF în Excel – lucruri de reținut
Deoarece scopul acestui tutorial este de a acoperi toate modalitățile posibile de a suma valorilor prin mai multe condiții, vom discuta exemple de formule cu ambele funcții – Excel SUMIFS și SUMIF cu mai multe criterii. Pentru a le utiliza corect, trebuie să înțelegeți clar ce au în comun aceste două funcții și în ce fel sunt diferite.
În timp ce partea comună este clară – destinație și parametri similari – diferențele nu sunt atât de evidente, deși foarte esențiale.
1. Ordinea argumentelor
În funcțiile Excel SUMIF și SUMIFS, ordinea argumentelor este diferită. În special, sum_range este prima parametru în SUMIFS, dar este al 3-lea în SUMIF.
La prima vedere, poate părea că Microsoft complică în mod deliberat curba de învățare pentru utilizatorii săi. Howev er, la o privire mai atentă, veți vedea raționamentul din spatele acestuia. Ideea este că sum_range este opțional în SUMIF. Dacă o omiteți, nu există nicio problemă, formula dvs. SUMIF va însuma valorile din interval (primul parametru).
În SUMIFS, sum_range este foarte important și obligatoriu și de aceea este pe primul loc. Probabil că băieții Microsoft au crezut că, după adăugarea perechii 10/100 gama / criterii, cineva ar putea uita să specifice intervalul pe care să-l însumăm:)
2. Intervalul de sumă și intervalul de criterii ar trebui să fie de dimensiuni egale
În funcția SUMIF, argumentul sum_range nu trebuie neapărat să aibă aceeași dimensiune ca argumentul intervalului, atâta timp cât aveți celula stângă sus dreapta. În Excel SUMIFS, fiecare criteriu_interval trebuie să conțină același număr de rânduri și coloane ca parametrul sum_range.
De exemplu, =SUMIF(A2:A9,F1,C2:C18) va returna rezultatul corect deoarece Excel consideră numai celula din stânga sus în argumentul sum_range (C2 în acest exemplu, care este corect), și apoi include atâtea coloane și rânduri cât conține argumentul range.
Bine, strategie suficientă (adică teoria) , să intrăm în tactici (adică exemple de formulă:)
Cum se utilizează SUMIFS în Excel – exemple de formulă
Acum un moment, am discutat o formulă SUMIFS simplă cu două text criterii. În același mod, puteți utiliza SUMIFS Excel cu mai multe criterii exprimate prin numere, date, expresii logice și alte funcții Excel.
Exemplu 1. SUMIFS Excel cu operatori de comparație
În tabelul furnizorilor de fructe, să presupunem că doriți să însumați toate livrările efectuate de Mike cu cantitatea de 200 sau mai mult. Pentru a face acest lucru, utilizați operatorul de comparație „mai mare decât o r egal cu „(> =) în criteriile2 și obțineți următoarea formulă SUMIFS:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Am acoperit în detaliu toți operatorii de comparație posibili atunci când discutăm despre funcția Excel SUMIF, aceiași operatori poate fi utilizat în criteriile SUMIFS. De exemplu, următoarea formulă cu returnează suma tuturor valorilor din celulele C2: C9 care sunt mai mari sau egale cu 200 și mai mici sau egale cu 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Exemplul 2. Utilizarea Excel SUMIFS cu date
În cazul în care doriți să însumați valori cu mai multe criterii bazate pe data curentă, utilizați funcția TODAY () din SUMIFS după cum este demonstrat mai jos. Următoarea formulă însumează valorile din coloana D dacă o dată corespunzătoare din coloana C se încadrează în ultimele 7 zile, inclusiv astăzi:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
În mod similar, puteți utiliza funcția Excel SUMIF pentru a suma valorilor într-un interval de date dat. De exemplu, următoarea formulă SUMIFS adaugă valorile din celulele C2: C9 dacă o dată din coloana B se încadrează între 1-oct-2014 și 31-oct-2014, inclusiv.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Același rezultat poate fi obținut prin calcularea diferenței a două funcții SUMIF, așa cum este demonstrat în acest exemplu – Cum se utilizează SUMIF pentru a suma valorilor într-un interval de date dat. Cu toate acestea, Excel SUMIFS este mult mai ușor și mai ușor de înțeles, nu-i așa?
Exemplul 3. Excel SUMIFS cu celule goale și non-blank
Când analizați rapoarte și alte date, poate fi adesea necesar să adune valori corespunzătoare celulelor goale sau ne-goale.
Și acum, să vedem cum puteți utiliza o formulă SUMIFS cu criterii „necompletate” și „necompletate” pe reale date.
Să presupunem că aveți o dată de comandă în coloana B, o dată de livrare în coloana C și cantitate. în coloana D. Cum găsiți totalul produselor care nu au fost încă livrate? Adică, doriți să cunoașteți suma valorilor corespunzătoare celulelor ne-goale din coloana B și celulelor goale din coloana C.
Soluția este de a utiliza formula SUMIFS cu 2 criterii:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Utilizarea Excel SUMIF cu mai multe criterii SAU
După cum sa menționat la începutul acestui tutorial, funcția SUMIFS este proiectată cu logica AND. Dar ce se întâmplă dacă trebuie să sumezi valori cu mai multe criterii SAU, adică atunci când este îndeplinită cel puțin una dintre condiții?
Exemplul 1. SUMIF + SUMIF
Cea mai simplă soluție este să sumezi rezultatele returnate de mai multe funcții SUMIF. De exemplu, următoarea formulă arată cum să găsiți totalul produselor livrate de Mike și John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
După cum vedeți, prima funcție SUMIF adaugă cantitățile corespunzătoare „Mike”, cealaltă funcție SUMIF returnează sumele aferente „John” și apoi adăugați aceste 2 numere.
Exemplul 2. SUM & SUMIF cu un argument array
Soluția de mai sus este foarte simplă și poate face treaba rapidă atunci când există doar câteva criterii. Dar o formulă SUMIF + SUMIF poate crește enorm dacă doriți să însumați valori cu condiții multiple SAU. În acest caz, o abordare mai bună este utilizarea unui argument al criteriilor matricei în funcția SUMIF. Să examinăm această abordare acum.
Puteți începe prin enumerarea tuturor condițiilor dvs. separate prin virgule și apoi să includeți lista rezultată separată prin virgule în {paranteze curlate}, care se numește tehnic o matrice.
Argumentul matricii format din 3 valori forțează formula SUMIF să returneze trei rezultate separate, dar din moment ce scriem formula într-o singură celulă, acesta va returna doar primul rezultat – adică totalul produselor livrate de John . Pentru ca această abordare a criteriilor matrice să funcționeze, trebuie să folosiți încă un mic truc – să includeți formula SUMIF într-o funcție SUM, astfel:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
După cum vedeți, un criteriu de matrice face formula mult mai compactă în comparație cu SUMIF + SUMIF și vă permite să adăugați atâtea valori cât vă place în matrice.
Această abordare funcționează atât cu numerele, cât și cu valorile textului. De exemplu, dacă în loc de numele furnizorilor din coloana C, ați avut coduri de furnizor precum 1, 2, 3 etc. ., atunci formula dvs. SUMIF ar arăta similar cu aceasta:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Spre deosebire de valorile textului, numerele nu trebuie să fie cuprinse între ghilimele duble în argumente matrice.
Exemplul 3. SUMPRODUCT & SUMIF
În caz, modul preferat este să listați criteriile în unele celule, mai degrabă că specificați-le direct în formulă, puteți utiliza SUMIF împreună cu funcția SUMPRODUCT care înmulțește componentele din matricile date și returnează suma acelor produse.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Unde G2: G4 sunt celulele care conțin criteriile dvs., numele furnizorilor în cazul nostru, așa cum este ilustrat în captura de ecran de mai jos.
Dar, bineînțeles, nimic nu vă împiedică să listarea valorilor într-o serie de criterii ale funcției SUMIF dacă doriți:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Rezultatul returnat de ambele formule va fi identic cu ceea ce vedeți în captura de ecran:
SUMIFS Excel cu criterii multiple SAU
Dacă doriți să sumați condiționat valorile în Excel nu doar cu condiții multiple SAU, ci cu mai multe seturi de condiții, va trebui să utilizați SUMIFS în loc de SUMIF. Formulele vor fi foarte asemănătoare cu ceea ce tocmai am discutat.
Ca de obicei, un exemplu ar putea ajuta la o mai bună ilustrare a punctului. În tabelul nostru de furnizori de fructe, să adăugăm Data livrării (coloana E) și găsiți cantitatea totală livrată de Mike, John și Pete în octombrie.
Exemplul 1. SUME + SUME
Formula produsă prin această abordare include o mulțime de repetări și aspect greoaie, dar este ușor de înțeles și, cel mai important, funcționează:)
Exemplul 2. SUM & SUMIFS cu un argument array
m încercat să explic esența acestei abordări în exemplul SUMIF, așa că acum putem pur și simplu să copiem acea formulă, să schimbăm ordinea argumentelor (așa cum vă amintiți că este diferit în SUMIF și SUMIFS) și să adăugăm criterii suplimentare. formula rezultată este mai compactă decât SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Rezultatul returnat de această formulă este exact același cum vedeți în captura de ecran de mai sus.
Exampl e 3. SUMPRODUCT & SUMIFS
După cum vă amintiți, abordarea SUMPRODUCT diferă de cele două anterioare prin modul în care introduceți fiecare dintre criteriile dvs. într-o celulă separată mai degrabă că le specifică direct în formulă. În cazul mai multor seturi de criterii, funcția SUMPRODUCT nu va fi suficientă și va trebui să folosiți ISNUMBER și MATCH.
Deci, presupunând că numele consumabilelor se află în celulele H1: H3, Data de început este în celula H4 și Data de încheiere în celula H5, formula noastră SUMPRODUCT ia următoarea formă:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Mulți oameni se întreabă de ce folosesc liniuța dublă (-) în formulele SUMPRODUCT. Ideea este că Excel SUMPRODUCT ignoră toate valorile, cu excepția numerelor, în timp ce operatorii de comparație din formula noastră returnează valori booleene (TRUE / FALSE), care sunt nenumerice. Pentru a converti aceste valori booleene la 1 „s și 0” s, utilizați semnul dublu minus, care se numește tehnic operatorul dublu unar. Primul constrânge unar TRUE / FALSE la -1/0, respectiv. Al doilea unar neagă valorile, adică inversează semnul, transformându-le în +1 și 0, pe care funcția SUMPRODUCT le poate înțelege.
Sper că explicația de mai sus are sens. Și chiar dacă nu, nu uitați doar această regulă generală – utilizați operatorul dublu unar (-) atunci când utilizați operatori de comparație în formulele SUMPRODUCT.
Utilizarea Excel SUM în formulele matrice
După cum vă amintiți, Microsoft a implementat funcția SUMIFS în Excel 2007. Dacă cineva folosește în continuare Excel 2003, 2000 sau versiuni anterioare, va trebui să utilizați o formulă de matrice SUM pentru a adăuga valori cu mai multe criterii ȘI. Bineînțeles, această abordare funcționează și în versiunile moderne ale Excel 2013 – 2007 și poate fi considerat un omolog de modă veche al funcției SUMIFS.
În formulele SUMIF discutate mai sus, ați folosit deja argumente matrice, dar o formulă matrice este ceva diferit.
Exemplul 1. Suma cu mai multe criterii ȘI în Excel 2003 și versiuni anterioare
Să revenim la primul exemplu în care am aflat o sumă a sumelor aferente către un anumit fruct și furnizor:
După cum știți deja, această sarcină se realizează cu ușurință utilizând un formula SUMIFS obișnuită:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Și acum, să vedem cum poate fi îndeplinită aceeași sarcină în primele versiuni „fără SUMIFS” ale Excel . În primul rând, scrieți toate condițiile care trebuie îndeplinite sub forma range = „condiție”. În acest exemplu, avem două perechi de intervale / condiții:
Condiția 1: A2: A9 = „mere”
Condiția 2: B2: B9 = „Pete”
Apoi, scrieți o formulă SUM care „înmulțește” toate perechile dvs. de domeniu / condiție, fiecare încadrată între paranteze. Ultimul multiplicator este intervalul sumă, C2: C9 în cazul nostru:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
După cum este ilustrat în captura de ecran de mai jos, formula funcționează perfect în cea mai recentă versiune Excel 2013.
Exemplul 2. Formule matrice SUM în versiunile moderne Excel
Chiar în versiunile moderne ale Excel, puterea funcției SUM nu ar trebui subestimată. Formula matricei SUM nu este doar gimnastică a minții, ci are o valoare practică, așa cum se demonstrează în exemplul următor.
Să presupunem , aveți două coloane, B și C, și trebuie să numărați de câte ori coloana C este mai mare decât coloana B, atunci când o valoare din coloana C este mai mare sau egală cu 10. O soluție imediată care vă vine în minte este utilizarea SUM formula matricei:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Nu vedeți niciun aspect practic aplicarea la formula de mai sus? Gândiți-vă la asta într-un alt mod:)
Să presupunem că aveți lista de comenzi așa cum se arată în captura de ecran de mai jos și doriți să știți câte produse nu au fost livrate în totalitate la o dată dată. Tradus în limbajul Excel, avem următoarele condiții:
Condiția 1: o valoare în coloana B (articole comandate) este mai mare decât 0
Condiția 2: o valoare în coloană C (Livrat) în mai puțin decât în coloana B
Condiția 3: o dată în coloana D (scadență) este mai mică decât 01.01.2014.
Punerea celor trei intervale / condiții perechi împreună, veți obține următoarea formulă:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Ei bine, exemplele de formule discutate în acest tutorial au zgâriat doar suprafața a ceea ce pot face cu adevărat funcțiile Excel SUMIFS și SUMIF. Dar, din fericire, acestea v-au ajutat să vă îndrepte în direcția corectă și acum puteți suma sumelor din registrele de lucru Excel, indiferent câte condiții complicate trebuie să ia în considerare.
- Cum se utilizează SUMIF în Excel – exemple de formulă pentru a suma condiționată a celulelor
- Cum se utilizează SUMIFS și SUMIF Excel cu mai multe criterii – exemple de formulă
- Cum se utilizează funcțiile VLOOKUP & SUM sau SUMIF în Excel
- SUMIF în Foi de calcul Google cu exemple de formulare
- COUNTIF în Excel – contați dacă nu gol, mai mare decât, duplicat sau unic
- Cum se numără și se sumează celulele după culoare în Excel