SUMIFS-functie, hoe u de functie kunt gebruiken om meerdere voorwaarden in Excel op te tellen

De Sumifs-functie in Excel is uiterst handig als u veel voorwaarden moet optellen. Hieronder vindt u details over het gebruik van de Sumifs-functie in Microsoft Excel .

De SUMIFS-functie in Excel is een van de basisfuncties van Excel , een veelgebruikte rekenfunctie in Excel. Om het totaal in Excel te berekenen gebruiken we de functie SOM . Als we een bepaalde voorwaarde aan de totaalfunctie willen toevoegen, gebruiken we de functie SUM.ALS . In gevallen waarin de gegevenstabel een som met veel voorwaarden vereist, moeten we de functie SUMIFS gebruiken. In het onderstaande artikel wordt uitgelegd hoe u de SUMIFS-functie in Excel gebruikt.

• MS Excel - Les 5: Excel-formules en -functies
• Hoe de Sumif-functie en de Vlookup-functie in Excel te combineren
• 3 manieren om totalen te berekenen in Excel
• Hoe de fout op te lossen dat de SOM-functie niet wordt toegevoegd in Excel

Inhoudsopgave van het artikel

• SUMIFS-functiesyntaxis in Excel
• Let op bij gebruik van de SUMIFS-functie in Excel
• Instructies voor het gebruik van de SUMIFS-functie in Excel
  • Gebruik jokertekens
  • Gebruik benoemde bereiken met de SUMIFS-functie
  • Voorbeeld van de SUMIFS-functie in Excel
• Beperkingen van de SUMIFS-functie

SUMIFS-functiesyntaxis in Excel

De syntaxis van de functie SUMIFS in Excel is =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...).

Daarin:

• Sum_range: zijn de cellen die moeten worden opgeteld in de gegevenstabel, lege waarden en tekstwaarden worden genegeerd, vereiste parameters.
• Criteria_bereik1: het bereik dat moet worden getest met behulp van het criterium criteria1, is een vereiste waarde.
• Criteria1: voorwaarde toegepast op criteria_bereik1, kan een getal, uitdrukking of celverwijzing zijn om te bepalen welke cellen in criteria_bereik1 worden opgeteld, ook een vereiste waarde.
• Criteria_range2, criteria2,…: optionele aanvullende bereiken en voorwaarden, maximaal 127 paren criteria_range, criteria.

Let op bij gebruik van de SUMIFS-functie in Excel

• Voor elke cel in het bereik wordt het bereik 'sum_range' opgeteld wanneer aan alle overeenkomstige voorwaarden is voldaan waarvan is vastgesteld dat ze waar zijn voor die cel.
• Cellen in som_bereik die TRUE bevatten, worden als 1 beschouwd, cellen die FALSE bevatten, worden als 0 beschouwd.
• Elk criterium_bereik moet dezelfde selectiegrootte hebben als som_bereik, criteria_bereik en som_bereik moeten hetzelfde aantal rijen en hetzelfde aantal kolommen hebben.
• Criteria kunnen een vraagteken (?) gebruiken in plaats van een enkel teken, een asterisk (*) in plaats van een tekenreeks. Als de voorwaarde een vraagteken of een asterisk is, moet u een ~-teken ervoor invoeren. De waarde van de voorwaarde is de tekst tussen ".
• Er worden meerdere voorwaarden toegepast met behulp van EN-logica, d.w.z. criteria1 EN criteria2, enz.
• Elk extra bereik moet hetzelfde aantal rijen en kolommen hebben als het totale bereik, maar de bereiken hoeven niet aaneengesloten te zijn. Als u niet-overeenkomende bereiken opgeeft, krijgt u de foutmelding #VALUE.
• De tekstreeks in de criteria moet tussen aanhalingstekens ("") staan, d.w.z. "apple", ">32", "jap*"
• Celverwijzingen in criteria mogen niet tussen aanhalingstekens staan, d.w.z. "<>
• SUMIF en SUMIFS kunnen bereiken verwerken, maar kunnen geen arrays verwerken. Dit betekent dat u geen andere functies, zoals JAAR, in het criteriabereik kunt gebruiken, omdat het resultaat een array is. Als u deze functionaliteit nodig heeft, gebruikt u de functie SUMPRODUCT.
• De volgorde van de argumenten tussen de functies SUMIFS en SUMIF is verschillend. Sombereik is het eerste argument in SUMIFS, maar het derde argument in SUMIF.

Instructies voor het gebruik van de SUMIFS-functie in Excel

Gebruik jokertekens

Jokertekens zoals '*' en '?' kan worden gebruikt in het criteria-argument bij gebruik van de SUMIFS-functie. Door deze jokertekens te gebruiken, kunt u vergelijkbare, maar niet exacte overeenkomsten vinden.

Asterisk (*) - Komt overeen met elke tekenreeks die na, vóór of met omringende criteria kan worden gebruikt om gedeeltelijk gebruik van zoekcriteria mogelijk te maken.

Als u bijvoorbeeld de volgende criteria toepast in de SUMIFS-functie:

• N * - Impliceert alle cellen in het bereik dat begint met N
• * N - Impliceert alle cellen in het bereik dat eindigt op N
• *N* - Cellen die N

Vraagteken (?) - Komt overeen met elk afzonderlijk teken. Stel dat je N?r als criterium hanteert. "?" hier wordt een enkel teken vervangen. N?r komt overeen met Noord, Nor, etc. Er wordt echter geen rekening gehouden met Naam.

Wat als de gegeven gegevens een asterisk of een echt vraagteken bevatten?

In dit geval kunt u de tilde (~) gebruiken. In dat geval moet u “~” vóór het vraagteken typen.

Gebruik benoemde bereiken met de SUMIFS-functie

Benoemd bereik is de beschrijvende naam van een reeks cellen of een celbereik in een werkblad. U kunt benoemde bereiken gebruiken terwijl u de SUMIFS-functie gebruikt.

Voorbeeld van de SUMIFS-functie in Excel

Het totaal berekenen we met onderstaande gegevenstabel onder een aantal verschillende voorwaarden.

Voorbeeld 1 : Bereken het totale aantal producten dat de werknemer van Hoai verkoopt met een eenheidsprijs van minder dan 400.000 VND.

In het resultateninvoervak ​​voeren we de formule in =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") en vervolgens = "" druk op = "">

Daarin:

• D2:D7 is het gebied waar het totale item moet worden berekend.
• C2:C7 is het waardebereik van de voorwaarde voor de naam van de werknemer.
• “Hoai” is de voorwaarde voor de naam van de werknemer die het aantal items in gebied C2:C7 moet berekenen.
• E2:E7 is de staat van het artikel.
• “400.000” is de voorwaarde voor het bevatten van items in de gebieden E2:E7.

Het resultaat is het totale aantal items, zoals hieronder weergegeven.

Voorbeeld 2 : Bereken het totale aantal artikelen dat is verkocht door Russische werknemers met serienummer <>

In het resultateninvoervak ​​voeren we de formule in =SUMIFS(D2:D7,C2:C7,"=Rusland",A2:A7,"<5") en vervolgens = "" druk op = "" enter. = "" bij = "" naam = "" multiplication = "" tablet = "" "nga" = " enter = "" sign = "of" remove = "" sign = "even">

Als resultaat krijgen we het totale aantal items dat is verkocht door Russische werknemers met serienummers < 5="", wat neerkomt op 550 items="">

Voorbeeld 3 : Bereken het totale aantal producten dat door Russische werknemers is verkocht, exclusief sjaalproducten.

In het resultaatinvoervak ​​voeren we de formule =SUMIFS(D2:D7,C2:C7,“Rusland”,B2:B7,“<>sjaal”) in. Waarbij het <> teken wordt gebruikt om een ​​bepaald object als voorwaarde uit te sluiten in het datagebied.

Het resultaat is het totale aantal producten zoals hieronder weergegeven.

Voorbeeld 4:

Bereken het totaal van alle statistieken voor generatie 1 Water-type Pokemon:

Voorwaarde hierbij is dat Water en Generatie 1 zijn.

Opmerking: De volledige gegevensset gaat verder na rij 14, helemaal tot aan rij 759.

Hier zijn de stapsgewijze details:

1. Selecteer cel H3 .
2. Type =SUMIFS.
3. Dubbelklik op de opdracht SUMIFS.
4. Selecteer het totale bereik als C2:C759.
5. Type,
6. Selecteer bereik voor eerste voorwaarde B2:B759(type 1-waarde)
7. Selecteer criteria (cel F3, met waarde Water)
8. typ,
9. Selecteer bereik voor tweede voorwaarde uit D2:D759(Generatiewaarde)
10. Type,
11. Bepaal de criteria (cel G3 heeft waarde 1).
12. Druk op Enter .

Opmerking: u kunt meer voorwaarden toevoegen door stap 9 tot en met 12 te herhalen voordat u op Enter drukt.

Deze functie berekent nu de som van de totale statistieken voor de water-Pokemon van generatie 1. Deze functie kan worden herhaald voor toekomstige generaties om ze te vergelijken.

Hierboven vindt u enkele voorbeelden van het gebruik van de functie SUMIFS, waarbij de totale waarde wordt berekend met veel gecombineerde voorwaarden. We moeten de bereiken en bijbehorende voorwaarden goed rangschikken, zodat Excel de functieformule voor berekening kan herkennen.

Beperkingen van de SUMIFS-functie

De SUMIFS-functie heeft enkele beperkingen waar u rekening mee moet houden:

• De voorwaarden in SUMIFS worden gecombineerd met EN-logica. Met andere woorden: alle voorwaarden moeten WAAR zijn voordat een cel in het totaal wordt opgenomen. Om cellen op te tellen met OR-logica, kunt u in eenvoudige gevallen een alternatieve oplossing gebruiken.
• De SUMIFS-functie vereist werkelijke bereiken voor alle bereikargumenten; je kunt geen array gebruiken. Dit betekent dat je geen dingen kunt doen zoals het jaartal uit een datum in de SUMIFS-functie halen. Om de waarden die in het bereikargument verschijnen te wijzigen voordat de criteria worden toegepast, is de functie SUMPRODUCT een flexibele oplossing.
• SUMIFS is niet hoofdlettergevoelig. Om waarden op te tellen op basis van een hoofdlettergevoelige voorwaarde, kunt u een formule gebruiken die is gebaseerd op de functie SOMPRODUCT met de functie EXACT.

De meest gebruikelijke manier om de bovenstaande beperkingen te omzeilen is door de functie SUMPRODUCT te gebruiken. In de huidige versies van Excel is een andere optie het gebruik van de nieuwere BYROW- en BYCOL-functies.

Hoe u de #VALUE-fout in de SUMIFS-functie van Excel kunt oplossen

Probleem: de formule verwijst naar cellen in een gesloten werkmap

De SUMIF-functie die verwijst naar een cel of celbereik in een gesloten werkmap resulteert in een #WAARDE!-fout.

Opmerking: dit is een veel voorkomende fout. Deze melding kom je ook vaak tegen bij het gebruik van andere Excel-functies zoals AANTAL.ALS, AANTAL.ALS, AANTALBLANK...

Oplossing:

Open de werkmap in deze formule en druk op F9 om de formule te vernieuwen. U kunt het probleem ook oplossen door zowel de functies SOM als ALS in een matrixformule te gebruiken.

Probleem: de criteriareeks is langer dan 255 tekens

De functie SUMIFS retourneert verkeerde resultaten wanneer u tekenreeksen probeert te combineren die langer zijn dan 255 tekens.

Oplossing:

Maak het touw indien mogelijk korter. Als dat niet mogelijk is, gebruik dan de CONCATENATE-functie of de &-operator om de waarde in meerdere tekenreeksen te splitsen. Bijvoorbeeld:

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

Probleem: in SUMIFS is het argument criteria_range inconsistent met het argument sum_range.

Het bereikargument moet altijd hetzelfde zijn in SUMIFS. Dat betekent dat de argumenten criteria_range en sum_range naar hetzelfde aantal rijen en kolommen moeten verwijzen.

In het volgende voorbeeld retourneert de formule de totale dagelijkse appelverkoop in Bellevue. Het argument sum_range (C2:C10) komt echter niet overeen met het aantal rijen en kolommen in criteria_range (A2:A12 & B2:B12). Het gebruik van een formule =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)levert een #VALUE-fout op.

Oplossing:

Ga sum_rangenaar C2:C12 en probeer de formule opnieuw.

Ik wens je succes!