1. SUMIF in Excel-sintaxa de utilizare

=SUMIF (celulele_unde_caut, criteriu_de_cautare, [celulele_de_unde_adun])

Dupa cum vedeti, functia SUMIF are 3 argumente-primele 2 sunt obligatorii, iar cel de-al 3-lea este optional.

celulele_unde_caut – celulele care trebuie evaluate dupa criteriile dvs., de exemplu A1: A10.

criteriu_de_cautare – conditia care trebuie indeplinita. Criteriile pot fi furnizate sub forma unui numar, text, data, expresie logica, o referinta a celulelor sau o alta functie Excel. De exemplu, puteti introduce criterii precum „11”,  „nuci”, „04/15/2020”,  „<5” sau direct B10 (adresa celulei) etc.

Nota. Va rog sa retineti ca orice criteriu care contine simboluri matematice trebuie sa fie incluse in ghilimele duble („). Pentru criterii numerice, ghilimelele duble nu sunt necesare. De pilda: „>=350”, „<>Bucuresti” (adica diferit de Bucuresti), etc.

celulele_de_unde_adun – celulele pentru care se doreste insumarea daca criteriu_de_cautare este indeplinit. Acest argument este optional si trebuie sa-l utilizati numai daca doriti sa insumati alte celule decat cele definite in argumentul celulele_unde_caut. Daca omiterea argumentului celulele_de_unde_adun, Excel va aduna aceleasi celule carora le sunt aplicate criteriile (adica cele specificate in argumentul celulele_unde_caut).

2. Un exemplu simplu de formula cu SUMIF

Pentru a ilustra mai bine sintaxa functiei SUMIF, sa luam in considerare urmatorul exemplu. Sa presupunem ca aveti o lista de produse in coloana C si valorile facturate corespunzatoare din coloana D. Vreti sa stiti care este suma tuturor valorilor de facturare de la un anumit produs, de pilda de la produsul Carlota:

Sa vedem cum ar arata formula de calcul automat (folosind functia SUMIF) a valorilor de factura pentru produsul „Carlota”:

celulele_unde_caut:                      C5:C15

criteriu_de_cautare:                    „Carlota”

celulele_de_unde_adun:             D5:D15

Punem cele trei argumente de mai sus in formula care foloseste functia SUMIF:

Acest exemplu demonstreaza utilizarea cea mai simpla a functiei SUMIF. In loc de textul scris manual in formula („Carlota”), puteti include referinta (adresa) de celula unde se afla criteriul dvs. De exemplu, puteti scrie din nou formula de mai sus, astfel incat sa faca referire la celula care contine numele produsului:

=SUMIF(C5:C15, G4, D5:D15)

Observatie: Argumentul celulele_de_unde_adun (al treilea, adica D5:D15) nu trebuie neaparat sa aiba aceeasi dimensiune cu argumentul celulele_unde_caut (primul argument, adica C5:C1), poate avea un numar diferit de randuri si coloane. De exemplu, in formula SUMIF de mai sus, puteti furniza in loc de D5:D15 chiar si D5:D1000, iar rezultatul va fi in continuare acelasi. Cu toate acestea, cea mai buna practica este de a oferi un interval de dimensiuni egale pentru primul si al treilea argument.

3. Insumarea folosind operatorii de comparatie standard

Presupunem ca avem un tabel cu datele de mai jos:

Urmatorii operatori de comparatie se folosesc de regula pentru a cauta pe coloanele cu numere.

4. Formule Excel SUMIF cu criterii pe texte

Functia SUMIF va permite sa adunati valori in functie de criteriile de cautare puse pe alte coloane ce contin texte.

Cautarile pe coloane de tip text pot fi EXACTE sau PARTIALE

Iata cum ar arata in Excel introdusa formula de la ultimul exemplu prezentat mai sus:

=SUMIF(D2:D13,”<>*boomerangs*”,F2:F13)

5. Utilizarea operatorilor de comparatie cu referinte de celule

Daca doriti sa faceti un pas inainte si sa obtineti o formula SUMIF cu adevarat utila, puteti inlocui o valoare numerica sau un text din criterii cu o referinta de celula, astfel:

=SUMIF(C2:C13,”>” &E4)

In acest caz, nu va trebui sa schimbati formula pentru a insuma conditionat valorile pe baza unui alt criteriu – pur si simplu introduceti o noua valoare intr-o celula de referinta. De exemplu, in cazul nostru daca dorim sa insumam Valorile mai mari de 50, doar inlocuim in celula E4 valoarea 100 cu 50, iar rezultatul din celula E5 se va recalcula automat (vezi mai jos):

Observatie: Cand utilizati o expresie logica cu o referinta de celula, trebuie sa folositi ghilimele duble („”) pentru a porni un sir de text si ampersand (&) pentru a concatena si a termina sirul, de exemplu „>” & E4

Si mai util ar fi sa permitem calcularea automata fara a modifica in formula de fiecare data operatorul de comparatie (in cazul nostru semnul „>”)

Putem sa scoatem din formula semnul „>” si sa-l adaugam intr-o celula separata, permitand astfel utilizatorului sa schimbe dinamic operatorul de comparatie (vezi animatia de mai jos):

Iata cum am facut demonstratia de mai sus:

Pasul 1: In celula F2 am adaugat o lista de valori posibile (Folosind instrumentul Data Validation din ribbon-ul Data) – vezi animatia de mai jos:

Nota: Este posibil ca pe computerul dvs. sa trebuiasca sa puneti lista de operatori cu separatorul ; intre ei (nu cu , asa cum este la mine).

Pasul 2: Am modificat formula din celula H2 astfel incat operatorul de comparatie sa fie concatenat automat din celula F2:

=SUMIF(C2:C13,F2 &G2)

Daca din lista de la F2 se alege „<=”, formula de mai sus se transforma automat in :

=SUMIF(C2:C13,”>=” &G2)

6. Exemple SUMIF cu metacaractere (wildcards)

Daca dorim sa insumam doar pentru celulele ce contin, incep, se termina cu un TEXT anume sau daca dorim sa facem suma doar pentru celulele ce au un numar fix de caractere, trebuie sa stim sa folosim caracterele de inlocuire (asa numitele metacaractere sau wildcards).

Exista doua mari tipuri de wildcards in Excel:

Semnul asterix (*) care inlocuieste oricate caractere

Sau

Semnul intrebarii (?) care inlocuieste un singur caracter

Exemplul 6.1. Sume pe baza cautarilor partiale (cu metacaractere / wildcards)

Se da tabelul cu produse si cantitatile lor vandute de mai jos:

Calculam suma cantitatilor pentru toate produsele ce au in denumire cuvantul „mere”:

=SumIF(A2:A10, „*mere*”, B2:B10)

 – rezultatul este 149

Calculam suma cantitatilor pentru toate produsele la care numele INCEPE cu cuvantul „mere”:

=SumIF(A2:A10, „mere*”, B2:B10)

 – rezultatul este 124

Calculam suma cantitatilor pentru toate produsele la care numele se termina cu cuvantul „mere”:

=SumIF(A2:A10, „*mere”, B2:B10)

 – rezultatul este 25

Calculam suma cantitatilor pentru toate produsele ce au fix 9 litere in denumire:

=SumIF(A2:A10, „?????????”, B2:B10)

 – rezultatul este 90

Calculam suma cantitatilor pentru toate produsele la care numele are litera n penultima in denumire”:

=SumIF(A2:A10, „*n?”, B2:B10)

 – rezultatul este 100

Desigur ca este recomandat sa nu scriem manual in formule ce anume cautam deoarece va fi greu sa modificam de fiecare data textul cautat.

In oricare exemplu de mai sus se poate folosi concatenarea (cu semnul &) pentru a referentia textul cautat aflat intr-o celula separata.

De exemplu, dorim suma cantitatilor pentru toate produsele ce contin in denumirea lor textul din celula E2:

=SUMIF(A2:A7, „*” & E2 & „*”, B2:B7)

Exemplul 6.2. Sume pe baza cautarilor bazate doar pe texte

Se poate intampla ca o coloana sa contina tipuri diferite de date (numere, texte, date calendaristice, etc).

Coloana Produs de mai jos contine inclusiv celule lasate necompletate (blank), celule goale (empty), celule cu numere, celula cu date calendaristice, valori booleene (true/false), dar si texte. Practic, am pus pe coloana unde o sa cautam toate tipurile de date din Excel.

Problema noastra este: cum adunam cantitatile doar pentru denumirile de produse ce contin TEXT (nu numere sau orice altceva).

O alta problema va fi: dorim sa luam in calcul si celulele lasate necompletate (blank) sau celulele goale (empty)?

Sa le luam pe rand:

Cum folosesc SUMIF ca sa insumez doar celulele corespondente textelor (nu numere sau orice altceva):

=SUMIF(A2:A12,”?*”,B2:B12)

 – rezultatul este 211 (vezi in poza de mai jos care au fost celulele adunate)

Atentie: nu s-au adunat si celulele corespunzatoare valorilor BLANK sau EMPTY

Exemplul 6.3: Cum caut/insumez in Excel chiar dupa caracterele wildcard (*, ?) :

Va rog sa urmariti tabelul de mai jos:

Daca as scrie formula:

=SUMIF(A2:A10,”?”,B2:B10)

 – ne-ar da rezultatul 48 pentru ca exista trei produse ce au denumirea formata dintr-o singura litera (in cazul acesta ? este interpretat ca wildcard care inlocuieste ORICE litera)

Mai exista un caracter wildcard, este vorba despre simbolul TILDA (~) care pus in fata unui alt wildcard (? Sau *) il face pe acesta din urma un caracter obisnuit:

=SUMIF(A2:A10,”~?”,B2:B10)

 –rezultatul este cel corect (26)

Semnul ~ se mai numeste si wildcard de tip escape, pentru ca poate „nega” celelalte wildcards (chiar si pe el insusi)

De exemplu:

=SUMIF(A2:A10,”~~?”,B2:B10)

 – returneaza sumele produselor care incep cu semnul ~ si care au maxim 2 caractere!

De ce? Primul ~ il face pe al doilea ~ un caracter de cautare obisnuit, iar cum ? are in fata sa un caracter obisnuit inseamna ca el este un wildcard adevarat (inlocuieste un caracter si numai unul) 😊

7. Cum insumez cele mai mari sau cele mai mici valori dintr-o coloana Excel?

Pentru o mai buna exemplificare schimb tabelul initial cu cel de mai jos:

Exemplul 7.1 Sa se calculeze Suma TOP 3 Costuri:

=SUMIF(C2:C13,”>=” & LARGE(C2:C13, 3))

Explicatii: cea mai mare a treia valoare de pe TOATA coloana Cost este 178 (este calculata prima cu functia Large)

Apoi SUMIF determina care dintre costuri este mai mare sau egal cu acel 178 si le aduna

 

Exemplul 7.2 Sa se calculeze Suma BOTTOM 5 Costuri:

=SUMIF(C2:C13,”>=” & SMALL(C2:C13, 5))

 – returneaza 148,32

Explicatii: cea mai mica a cincea valoare de pe coloana Cost este 54.62

Apoi SUMIF determina care dintre costuri este mai mic sau egala cu acel 54,62 si le aduna

Pentru o mai buna urmarire a valorilor am sortat tabelul dupa coloana COST

8. Cum insumez celulele blank/empty

Exemplul 8.1: Cum folosesc SUMIF ca sa insumez doar celulele corespondente textelor BLANK sau EMPTY („”)

=SUMIF(A2:A10,””,B2:B10)

 – rezultatul este 44 (include atat celulele lasate libere, adica fara nicio formula sau valoare in ele, dar si celulele contin sirul gol („”) sau care au formule ce intorc „”). Vezi poza de mai jos:

Exemplul 8.2: Cum folosesc SUMIF ca sa insumez doar celulele corespondente textelor BLANK (NU si empty „”)

=SUMIF(A2:A10,”=”,B2:B10)

 – rezultatul este 12 pentru ca avem doar o celula BLANK

Exemplul 8.3: Cum folosesc SUMIF ca sa insumez doar celulele corespondente textelor Empty („”)

=SUMIF(A2:A10,””,B2:B10)- SUMIF(A2:A10,”=”,B2:B10)

 – rezultatul este 32 (am scazut (BLANK + EMPTY)- BLANK) 😊

Exemplul 8.4: Cum folosesc SUMIF ca sa adun celulele fara BLANKS?

=SUMIF(A2:A10,”<>”,B2:B10)

 – nu ia in considerare BLANK, insa le ia la socoteala pe cele empty

Exemplul 8.5 Cum folosesc SUMIF ca sa adun celulele fara BLANKS si fara Empty (adica care au ceva in ele, orice insa nu goale sau blank)?

=SUM(B2:B10) – SUMIF(A2:A10,””,B2:B10)

 – adica am scazut din suma TOTALA a celulelor pe cele care au Blank sau empty

9. Utilizarea functiei SUMIF cu datele calendaristice

In cazul in care doriti sa insumati valorile pe baza datei curente, atunci trebuie sa utilizati functia SUMIF in combinatie cu functia TODAY (), astfel:

Cum insumez valorile corespondente aflate intre doua date?

Doua metode:

Metoda 1: Cu o coloana ajutatoare

Pasul 1: Adaugam o coloana ajutatoare in tabel care returneaza cu ajutorul functiei logice AND daca data se afla in intervalul dorit

=AND(A2>=$F$7, A2<=$F$8)

Pasul 2: Folosim SUMIF si adunam valorile corespondente de pe coloana ajutatoare (cautam valoarea TRUE)

=SUMIF(D2:D13, TRUE, C2:C13)

 – adunam costurile ce au TRUE pe coloana ajutatoare

Metoda 2: Folosim o alta functie care stie sa insumeze dupa mai multe conditii logice simultan!

Ati ghicit? Este vorba despre SUMIFS (la plural, are un S la coada)

Despre functia SUMIFS vorbesc in partea II (pentru avansati)

10. Inlocuirea functiei SUMIF cu functia SUBTOTAL + Filtru

Am lasat special pentru final o modalitate foarte usoara de a agrega conditionat.

Avand in vedere ca totusi acest articol a fost realizat cu gandul la cei cu mai putina experienta in Excel, daca nu arat acum aceasta metoda nu o sa mai am cand 😊

Vom folosi functia Subtotal pentru a aduna datele dintr-un tabel filtrat.

De exemplu, dorim sa calculam suma costurilor doar pentru produsul Aspen:

Pasul 1: Inainte de a scrie vreo formula filtram tabelul de mai sus cautand Produsul Aspen:

Pasul 2: Dupa ce am filtrat, ne pozitionam pe prima celula disponibila de pe coloana Cost, apoi apasam butonul AutoSUM sau si mai bine apasam simultan ALT cu semnul egal (ALT + =) (vedeti animatia de mai jos)

=SUBTOTAL(9,F2:F13)

In formula =SUBTOTAL(9,F2:F13), primul argument (acel 9) inseamna ca dorim suma tuturor celulelor vizibile de pe coloana F2:F13

Si cum vizibile sunt doar liniile stabilite de filtru, iata cum am reusit sa calculam o suma conditionata (semi)automat

Partea buna este ca daca schimbati conditiile filtrului se recalculeaza automat si suma costurilor pentru liniile ce raman pe ecran

Exemplele SUMIF descrise in acest articol ating doar unele dintre utilizarile de baza ale acestei functii.

In articolul urmator (SUMIFS, agregari conditionate pentru avansati), vom investiga formule mai sofisticate, din care o sa rezulte puterea reala a functiilor SUMIF si SUMIFS (ce permit sa insumati dupa ORICATE criterii logice).

Va multumesc!