Adunarea celulelor in functie de doua sau mai multe conditii

Am revenit cu postarea despre adunarea celulelor in functie de doua sau mai multe conditii. Pentru a realiza acest lucru putem folosi functia SUMPRODUCT sau functia SUMIFS, insa cea din urma este disponibila doar pentru pachetele Office 2007 si 2010. 

• Functia SUMPRODUCT -  la baza, aceasta functia multiplica celulele corespondente ale unor zone de celule date si returneaza suma acelor multiplicari. Insa cunoscand sintaxa potrivita functia poate aduna sau numara celulele care indeplinesc doua sau mai multe criterii. Deoarece sintaxa SUMPRODUCT difera de cea de baza, pentru a intelege mai bine o sa fac o prezentare direct pe exemplu.

Pentru aceasta postare folosesc un fisier in care tabelul cu informatii se regaseste in sheet-ul Data. In tabelul alaturat puteti vedea o portiune din acesta.

Pentru exemplul nostru am ales sa calculam Venitul Total Realizat pentru fiecare Regiune si fiecare Produs. Pentru a realiza suma in functie de aceste doua criterii ne vom folosi de argumentele de tip array in cadrul functiei sumproduct.

=SUMPRODUCT(--(Data!$C$2:$C$1000=A2),--(Data!$D$2:$D$1000=B2),

--(Data!$F$2:$F$1000))

1. Selectam celula in care dorim sa realizam Venitul Total Realizat.
2. Scrien = Sumproduct(.
3. Scriem argumentul pentru prima conditie. Asa cum am precizat anterior le vom scrie de tip array si pentru acest lucru este sintaxa --(selectie celule=conditie). In sheet-ul Data selectam celulele de pe coloana Regiune, scriem = si apoi selectam celula care contine prima conditie, in cazul nostru A2 si inchidem paranteza. Spre deosebire de sumif, in sumproduct nu putem selecta o coloana intreaga. De aceea am selectat regiune C2:C1000 pe care am facut-o referinta. 
4. Scriem argumentul pentru conditia. Scriem --(, apoi selectam in sheet-ul  Data celulele de pe coloana Produselor, scriem = si apoi selectam celula care contine a doua conditie, in cazul nostru B2, si inchidem paranteza. La fel ca mai sus nu putem selecta o coloana intreaga si de aceea selectam doar celulele D2:D1000 pe care le facem referinta.
5. Selectam zona de celule pe care dorim sa le adunam. Scriem --(, selectam in sheet-ul  Data celulele de pe coloana Venitului realizat si inchidem paranteza. La fel ca mai sus nu putem selecta o coloana intreaga si de aceea selectam doar celulele F2:F1000 pe care le facem referinta.
6. Apasam ENTER.

Pentru a completa Venitul Total Realizat si pentru restul de produse si regiuni doar tragem celula C2 pana la C24.

 Observatii:

•  Argumentele de tip array trebuie să aibă aceeasi dimensiune. In caz contrar, SUMPRODUCT întoarce valoarea de eroare #VALUE!.
• Cand selectati zonele de celule pentru conditii si suma nu selectati si capul de tabel, in cazul in care exista.
• Cand scriu in cadrul SUMPRODUCT zona de celule pentru conditii si suma, selectez o zona mai mare pentru ca, in cele mai multe cazuri, tabelul este dinamic.
• Daca in cadrul tabelului aveti si celule goale si primiti eroarea #VALUE! puteti incerca sa completati 0 in toate celulele goale. 
• Celulele care sunt evaluate conform conditiei stabilete nu trebuie neaparat sa fie pe o coloana ele putand fi si un rand. 
• In prezentarea functiei SUMPRODUCT am scris ca aceasta se poate folosi si pentru a numara celulele care indeplinesc doua sau mai multe conditii. Pentru a realiza acest lucru folosim sintaxa de mai sus dar fara a mai selecta zona de celule pe care sa le adune formula. 

Desi functia SUMPRODUCT este foarte folositoare, ea este destul de pretentioasa si cand o folosim putem primi cateva erori pana sa functioneze cum trebuie. De aceea introducerea functie SUMIFS a fost o adevarata binecuvantare pentru utilizatorii de Excel.

•  Functia SUMIFS - aceasta functie se foloseste pentru insumarea celulelor care indeplinesc una sau mai multe conditi. Sintaxa pentru SUMIFS este urmatoarea:      

  =SUMIFS(SumRange, ConditionRange1, Criteria1, ConditionRange2, Criteria2,...) 

1.  SumRange - reprezinta celulele pe care dorim sa le adunam.
2. ConditionRange1 - reprezinta zona de celule care vor fi evaluate in functie de conditia 1.
3. Criteria1 - reprezinta conditia 1 care defineste celulele care vor fi adunate.
4. Criteria_range2, criteria2, …  - sunt argumente optionale si reprezinta alte conditii si celule de comparare suplimentare. Pot fi introduse pana la 127 perechi de ConditionRange si Criteria.  
   

In exemplul nostru dorim sa aflam pentru Venitul Total Realizat anual pentru fiecare regiune si folosim urmatoarea sintaxa: =SUMIFS(Data!$F:$F,Data!$C:$C,Sumifs!$A3,Data!$H:$H,Sumifs!B$2).

Spre deosebire de SUMPRODUCT, in cadrul SUMIFS putem selecta toata coloana dintr-un tabel atat pentru SumRange cat si pentru ConditionRange. In schimb, ConditionRange nu poate fi un rand.

Pentru a intelege mai usor cum putem insuma o zona de celule folosind doua sau mai multe conditii puteti downloada fisierul cu exemplul de la urmatorul link: SumCells.xlsx.