Funcția SUMIFS, cum să utilizați funcția pentru a însuma mai multe condiții în Excel

Funcția Sumifs din Excel este extrem de utilă atunci când trebuie să însumați multe condiții. Mai jos sunt detalii despre cum să utilizați funcția Sumifs în Microsoft Excel .

Funcția SUMIFS din Excel este una dintre funcțiile de bază Excel , o funcție de calcul folosită în mod obișnuit în Excel. Pentru a calcula totalul în Excel, vom folosi funcția SUM .Dacă dorim să adăugăm o anumită condiție la funcția total, vom folosi funcția SUMIF . În cazurile în care tabelul de date necesită o sumă cu multe condiții, trebuie să folosim funcția SUMIFS. Articolul de mai jos vă va ghida cum să utilizați funcția SUMIFS în Excel.

• MS Excel - Lecția 5: Formule și funcții Excel
• Cum să combinați funcția Sumif și funcția Vlookup în Excel
• 3 moduri de a calcula totalurile în Excel
• Cum să remediați eroarea funcției SUM care nu se adaugă în Excel

Cuprinsul articolului

• Sintaxa funcției SUMIFS în Excel
• Rețineți când utilizați funcția SUMIFS în Excel
• Instrucțiuni pentru utilizarea funcției SUMIFS în Excel
  • Folosiți metacaracterele
  • Utilizați intervale denumite cu funcția SUMIFS
  • Exemplu de funcție SUMIFS în Excel
• Limitări ale funcției SUMIFS

Sintaxa funcției SUMIFS în Excel

Sintaxa funcției SUMIFS în Excel este =SUMIFS(interval_suma, interval_criterii1, criterii1, interval_criterii2, criterii2,...).

Acolo:

• Sum_range: sunt celulele care trebuie însumate în tabelul de date, valorile goale și valorile text sunt ignorate, parametri necesari.
• Criteria_range1: intervalul care trebuie testat folosind criteriul criteria1, este o valoare necesară.
• Criterii1: condiție aplicată la criteria_range1, poate fi un număr, expresie, referință de celulă pentru a determina care celule din criteria_range1 vor fi însumate, de asemenea, o valoare necesară.
• Criteria_range2, criteria2,...: intervale și condiții suplimentare opționale, maxim 127 de perechi de criterii_range, criterii.

Rețineți când utilizați funcția SUMIFS în Excel

• Pentru fiecare celulă din interval, intervalul sum_range va fi însumat atunci când sunt îndeplinite toate condițiile corespunzătoare determinate a fi adevărate pentru acea celulă.
• Celulele din sum_range care conțin TRUE sunt considerate 1, celulele care conțin FALSE sunt considerate 0.
• Fiecare criteria_range trebuie să aibă aceeași dimensiune de selecție ca sum_range, criteria_range și sum_range trebuie să aibă același număr de rânduri și același număr de coloane.
• Criteriile pot folosi un semn de întrebare (?) în loc de un singur caracter, un asterisc (*) în loc de un șir. Dacă condiția este un semn de întrebare sau un asterisc, trebuie să introduceți semnul ~ în față, valoarea condiției este textul cuprins în „.
• Sunt aplicate mai multe condiții folosind logica AND, adică criteriul1 și criteriul2 etc.
• Fiecare interval suplimentar trebuie să aibă același număr de rânduri și coloane ca și intervalul total, dar intervalele nu trebuie să fie învecinate. Dacă furnizați intervale nepotrivite, veți primi o eroare #VALUE.
• Șirul de text din criterii trebuie să fie cuprins între ghilimele (""), adică "măr", ">32", "jap*"
• Referințele de celule din criterii nu pot fi incluse între ghilimele, adică „<>
• SUMIF și SUMIFS pot gestiona intervale, dar nu pot gestiona matrice. Aceasta înseamnă că nu puteți utiliza alte funcții precum YEAR în intervalul de criterii, deoarece rezultatul este o matrice. Dacă aveți nevoie de această funcționalitate, utilizați funcția SUMPRODUCT.
• Ordinea argumentelor dintre funcțiile SUMIFS și SUMIF este diferită. Intervalul sumei este primul argument în SUMIFS, dar al treilea argument în SUMIF.

Instrucțiuni pentru utilizarea funcției SUMIFS în Excel

Folosiți metacaracterele

Caractere wildcard precum „*” și „?” poate fi folosit în argumentul criteriilor când se utilizează funcția SUMIFS. Folosirea acestor metacaractere vă va ajuta să găsiți potriviri similare, dar nu exacte.

Asterisc (*) - Se potrivește cu orice șir de caractere, care poate fi folosit după, înainte sau cu criteriile înconjurătoare pentru a permite utilizarea parțială a criteriilor de căutare.

De exemplu, dacă aplicați următoarele criterii în funcția SUMIFS:

• N * - implică toate celulele din intervalul începând cu N
• * N - implică toate celulele din interval care se termină în N
• *N* - Celule care conțin N

Semnul întrebării (?) - Se potrivește cu orice caracter. Să presupunem că aplicați N?r ca criteriu. "?" aici va înlocui un singur caracter. N?r va corespunde Nordului, Norului etc. Cu toate acestea, nu va lua în considerare Nume.

Ce se întâmplă dacă datele furnizate conțin un asterisc sau un semn de întrebare real?

În acest caz, puteți utiliza tilde (~). În acest caz, trebuie să tastați „~” înainte de semnul întrebării.

Utilizați intervale denumite cu funcția SUMIFS

Interval denumit este numele descriptiv al unui set de celule sau al unui interval de celule dintr-o foaie de lucru. Puteți utiliza Intervalele denumite în timp ce utilizați funcția SUMIFS.

Exemplu de funcție SUMIFS în Excel

Vom calcula totalul cu tabelul de date de mai jos cu un număr de condiții diferite.

Exemplul 1 : Calculați totalul produselor pe care angajatul lui Hoai le vinde cu un preț unitar mai mic de 400.000 VND.

În caseta de introducere a rezultatului, introducem formula =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") și apoi="" press="">

Acolo:

• D2:D7 este zona în care trebuie calculat articolul total.
• C2:C7 este intervalul de valori pentru condiția numelui angajatului.
• „Hoai” este condiția pentru numele angajatului care trebuie să calculeze numărul de articole conținute în zona C2:C7.
• E2:E7 este starea articolului.
• „400.000” este condiția pentru a conține articole în zonele E2:E7.

Rezultatul este numărul total de articole, așa cum se arată mai jos.

Exemplul 2 : Calculați numărul total de articole vândute de angajații ruși cu numărul de serie <>

În caseta de introducere a rezultatului, introducem formula =SUMIFS(D2:D7,C2:C7,"=Rusia",A2:A7,"<5") și apoi="" apăsați="" enter.="" la = "" name="" multiplication="" tablet="" "nga"="" enter="" sign="or" remove="" sign="even">

Ca rezultat, obținem numărul total de articole vândute de angajații ruși cu numere de serie < 5="" care este 550 de articole="">

Exemplul 3 : Calculați numărul total de produse vândute de angajații ruși, excluzând produsele eșarfelor.

În caseta de introducere a rezultatului, introducem formula =SUMIFS(D2:D7,C2:C7,“Rusia”,B2:B7,“<>scarf”). În care semnul <> este folosit pentru a exclude un anumit obiect din zona de date ca o condiție.

Rezultatul este numărul total de produse, așa cum se arată mai jos.

Exemplul 4:

Calculați totalul tuturor statisticilor pentru generația 1 de Pokemon de tip apă:

Condiția aici este ca Apa și Generația să fie 1.

Notă: setul complet de date continuă după rândul 14, până la rândul 759.

Iată detaliile pas cu pas:

1. Selectați celula H3 .
2. Tip =SUMIFS.
3. Faceți dublu clic pe comanda SUMIFS.
4. Selectați intervalul total ca C2:C759.
5. Tip,
6. Selectați intervalul pentru prima condiție B2:B759(valoarea de tip 1)
7. Selectați criteriile (celulă F3, cu valoare Water)
8. tip ,
9. Selectați intervalul pentru a doua condiție din D2:D759(valoarea generației)
10. Tip,
11. Determinați criteriile (celula G3 are valoarea 1).
12. Apasa Enter .

Notă: Puteți adăuga mai multe condiții repetând pașii de la 9 la 12 înainte de a apăsa Enter.

Această funcție calculează acum suma statisticilor totale pentru generația 1 de Pokemon de apă. Această funcție poate fi repetată pentru generațiile viitoare pentru a le compara.

Mai sus sunt câteva exemple de utilizare a funcției SUMIFS, calculând valoarea totală cu multe condiții combinate. Trebuie să aranjam intervalele și condițiile însoțitoare în mod corespunzător, astfel încât Excel să poată recunoaște formula funcției pentru calcul.

Limitări ale funcției SUMIFS

Funcția SUMIFS are câteva limitări de care ar trebui să știți:

• Condițiile din SUMIFS sunt combinate folosind logica AND. Cu alte cuvinte, toate condițiile trebuie să fie TRUE pentru ca o celulă să fie inclusă în total. Pentru a suma celulele folosind logica SAU, puteți utiliza o soluție alternativă în cazuri simple.
• Funcția SUMIFS necesită intervale reale pentru toate argumentele intervalului; nu poți folosi o matrice. Aceasta înseamnă că nu puteți face lucruri precum extragerea anului dintr-o dată din cadrul funcției SUMIFS. Pentru a modifica valorile care apar în argumentul interval înainte de aplicarea criteriilor, funcția SUMPRODUCT este o soluție flexibilă.
• SUMIFS nu face distincție între majuscule și minuscule. Pentru a suma valorile bazate pe o condiție sensibilă la majuscule și minuscule, puteți utiliza o formulă bazată pe funcția SUMPRODUS cu funcția EXACT.

Cea mai obișnuită modalitate de a evita limitările de mai sus este utilizarea funcției SUMPRODUCT. În versiunile actuale de Excel, o altă opțiune este utilizarea funcțiilor BYROW și BYCOL mai noi.

Cum se remediază eroarea #VALUE în funcția SUMIFS din Excel

Problemă: Formula se referă la celule dintr-un registru de lucru închis

Funcția SUMIF care face referire la o celulă sau un interval de celule dintr-un registru de lucru închis va avea ca rezultat o eroare #VALOARE!.

Notă: Aceasta este o eroare comună. De asemenea, întâlniți adesea acest mesaj atunci când utilizați alte funcții Excel, cum ar fi COUNTIF, COUNTIFS, COUNTBLANK...

Soluţie:

Deschideți registrul de lucru conținut în această formulă, apăsați F9 pentru a reîmprospăta formula. De asemenea, puteți rezolva problema utilizând atât funcțiile SUM, cât și IF într-o formulă matrice.

Problemă: șirul de criterii are mai mult de 255 de caractere

Funcția SUMIFS returnează rezultate greșite atunci când încercați să combinați șiruri mai lungi de 255 de caractere.

Soluţie:

Scurtați șirul dacă este posibil. Dacă acest lucru nu este posibil, utilizați funcția CONCATENATE sau operatorul & pentru a împărți valoarea în mai multe șiruri. De exemplu:

=SUMIF(B2:B12,"long string"&"another long string")

Problemă: În SUMIFS, argumentul criteria_range este inconsecvent cu argumentul sum_range.

Argumentul interval trebuie să fie întotdeauna același în SUMIFS. Aceasta înseamnă că argumentele criteria_range și sum_range ar trebui să se refere la același număr de rânduri și coloane.

În exemplul următor, formula returnează vânzările zilnice totale de mere în Bellevue. Cu toate acestea, argumentul sum_range (C2:C10) nu corespunde numărului de rânduri și coloane din criteria_range (A2:A12 și B2:B12). Folosirea unei formule =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)va da o eroare #VALUE.

Soluţie:

Schimbați sum_rangela C2:C12 și încercați din nou formula.

Vă doresc succes!