SUMIFS()-functie in Excel: Tel gegevens op met meerdere criteria

De functie SUMIFS() in Excel telt getallen op op basis van meerdere voorwaarden, zodat je precieze controle krijgt over berekeningen. In tegenstelling tot SUMIF(), dat één voorwaarde controleert, verwerkt SUMIFS() meerdere voorwaarden tegelijk.

In dit artikel laat ik je zien hoe je SUMIFS() gebruikt met geavanceerde voorbeelden en tips voor probleemoplossing. Aan het eind weet je hoe je SUMIFS() inzet om je berekeningen nauwkeuriger en efficiënter te maken.

Het korte antwoord

Zo gebruik je de functie SUMIFS() in Excel:

• Typ =SUMIFS( om de functie te beginnen.

• Selecteer het bereik met de waarden die je wilt optellen.

• Selecteer het bereik waarop de voorwaarde wordt toegepast.

• Voer de te matchen voorwaarde in.

• Sluit de haakjes en druk op Enter.

Hier heb ik alle verkopen opgeteld die groter zijn dan 200.

=SUMIFS(B2:B8, B2:B8, ">200")

Tel de waarden op met SUMIFS(). Afbeelding door auteur.

De SUMIFS()-functie begrijpen

De functie SUMIFS() in Excel telt waarden alleen op wanneer aan alle opgegeven voorwaarden is voldaan. Hij ondersteunt logische operatoren zoals groter dan >, kleiner dan <, gelijk aan = en niet gelijk aan <>, evenals jokertekens voor gedeeltelijke overeenkomsten. De functie werkt met getallen, tekst en datums.

Syntaxis van SUMIFS()

De functie SUMIFS() definieert eerst het bereik met waarden om op te tellen, gevolgd door paren van voorwaardebereiken en criteria. Let op dat SUMIFS() meer dan één logische voorwaarde aankan.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Hierbij geldt:

• sum_range is het celbereik dat je wilt optellen.

• criteria_range1 is het eerste bereik waarin we op een voorwaarde controleren.

• criteria1 is de voorwaarde die wordt toegepast op criteria_range1.

• [criteria_range2, criteria2], … zijn optioneel. Je kunt meer paren van bereiken en voorwaarden toevoegen. Elk paar voegt een extra voorwaarde toe waaraan moet worden voldaan.

Let erop dat het criteria_range (het bereik waarin voorwaarden worden gecontroleerd) evenveel rijen en kolommen moet hebben als het sum_range (het bereik dat wordt opgeteld).

Voorbeeld van SUMIFS()

Laten we een eenvoudig voorbeeld proberen. Ik heb een dataset met fruitsoorten, hun hoeveelheden en verkopen. Ik wil de totale verkoop van Apples vinden waarbij de Quantity groter is dan 5. Mijn formule wordt dan:

=SUMIFS(C2:C8, A2:A8, "Apples", B2:B8, ">5")

Hier is C2:C8 het bereik met waarden om op te tellen (Sales ($)). A2:A8, "Apples" is de voorwaarde om alleen rijen op te nemen waar het product Apples is. En B2:B8, ">5" is de voorwaarde om alleen rijen op te nemen waar de Quantity groter is dan 5.

SUMIFS()-formule om de verkoop op te tellen op een voorwaarde. Afbeelding door auteur.

SUMIF() vs. SUMIFS()

Het belangrijkste verschil tussen SUMIF() en SUMIFS() is het aantal voorwaarden dat ze gebruiken. SUMIF() verwerkt één voorwaarde. Maar SUMIFS() kan meerdere voorwaarden aan.

Use-cases voor SUMIFS() in Excel

Nu je weet hoe de functie SUMIFS() werkt, bekijken we enkele geavanceerde gevallen waarin we dit in de praktijk kunnen gebruiken.

SUMIFS() gebruiken met verschillende gegevenstypen

SUMIFS() kan werken met verschillende gegevenstypen zoals getallen, tekst en datums. Stel, ik heb een verkooprapport, maar in sommige rijen ontbreken klantnamen. Ik wil alleen die rijen optellen waarbij we de naam van de klant hebben. Daarvoor gebruik ik de volgende formule:

=SUMIFS(C2:C5, A2:A5, "<>")

Deze formule kijkt naar de kolom Customer, controleert op niet-lege cellen en telt vervolgens de bijbehorende verkopen op.

Tel alle niet-lege cellen op met de functie SUMIFS(). Afbeelding door auteur.

Om de verkopen op te tellen waar klantnamen ontbreken, gebruik ik deze formule:  

=SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

Tel de lege cellen op met de functie SUMIFS(). Afbeelding door auteur.

In plaats van de voorwaarde hard te coderen, kunnen we die in een andere cel opslaan. Als ik de verkopen voor Sarah Lee wil optellen, typ ik die naam in cel E3 en gebruik ik deze formule:

=SUMIFS(C2:C5, A2:A5, E3)

Wanneer de waarde in E3 verandert, wordt het totaal automatisch bijgewerkt.

Gebruik SUMIFS() om waarden op te tellen met de celverwijzing. Afbeelding door auteur.

OR-logica toepassen in SUMIFS()

We weten dat SUMIFS() alleen EN-logica gebruikt, wat betekent dat alle ingestelde voorwaarden waar moeten zijn om de waarden op te tellen. Wil je echter OF-logica gebruiken, die waarden optelt wanneer aan één van meerdere voorwaarden is voldaan, dan kan dat door meerdere SUMIFS()-formules te combineren.

Als ik bijvoorbeeld de verkopen wil optellen voor ofwel John Doe OF Drake White, combineer ik twee SUMIFS()-formules zoals dit:

=SUMIFS(C2:C8, A2:A8, "John Doe") + SUMIFS(C2:C8, A2:A8, "Drake White")

Omdat Drake White niet in de lijst staat, geeft dat deel van de formule 0 terug. Het totaal omvat alleen de verkopen voor John Doe, wat laat zien hoe OF-logica werkt.

Gebruik OF-logica in de functie SUMIFS(). Afbeelding door auteur.

In Excel 365 of later kunnen we dit vereenvoudigen met een matrixformule en krijg je hetzelfde resultaat.

=SUMIFS(C2:C8,A2:A8,{"John Doe","Drake White"})

Gebruik de SUMIFS()-matrixformule. Afbeelding door auteur.

SUMIFS() met benoemde bereiken

We kunnen ook benoemde bereiken gebruiken in plaats van celverwijzingen zoals A2:A5 of C2:C5. In dit voorbeeld selecteer ik A2:A5 en noem dat Customer_list en ik selecteer C2:C5 en noem dat Sales_list. Nu om de totale verkoop van John te vinden, gebruik ik deze formule: 

=SUMIFS(Sales_list, Customer_list, "John")

Tel de waarden op met SUMIFS() met behulp van benoemde bereiken. Afbeelding door auteur.

Jokertekens gebruiken voor gedeeltelijke overeenkomsten

Jokertekens zijn speciale tekens (* of ?) die onbekende of variabele tekens vertegenwoordigen in tekstzoekopdrachten en patroonherkenning: 

• * komt overeen met elk aantal tekens (inclusief nul).

• ? komt overeen met precies één teken.

Zo werken ze: 

• A* matcht alle cellen die beginnen met A.

• *A matcht alle cellen die eindigen op A.

• *A* matcht alle cellen die A ergens bevatten.

De functie SUMIFS() biedt tekstmatching via jokertekens. Hij herkent waarden die beginnen met, eindigen op of specifieke tekstpatronen bevatten. Stel dat ik de verkopen wil optellen voor de Product Code die begint met ABC. In dat geval ziet mijn formule er zo uit:

=SUMIFS(D2:D8, C2:C8, "ABC*")

Om de verkopen op te tellen die eindigen op 00, schrijf ik:

=SUMIFS(B2:B7, A2:A7, "*00")

Gebruik jokertekens in SUMIFS(). Afbeelding door auteur.

Optellen op basis van datumbereiken

SUMIFS() kan ook waarden optellen binnen een specifiek datumbereik. Als ik alle verkopen van januari 2023 wil weten, gebruik ik de volgende formule:

=SUMIFS(D2:D8, B2:B8, ">=01-Jan-2023", B2:B8, "<=31-Jan-2023") 

Deze formule controleert de datums van 1 januari 2023 tot en met 31 januari 2023 en telt vervolgens alle verkopen op.

Tel het datumbereik op met SUMIFS(). Afbeelding door auteur.

Waarden optellen met meerdere numerieke voorwaarden

De functie SUMIFS() kan waarden optellen die binnen specifieke numerieke bereiken vallen. Hier heb ik een willekeurige dataset en ik wil de waarden optellen waarbij Sales groter is dan 100 maar kleiner dan 300. Hiervoor gebruik ik de volgende formule:  

=SUMIFS(C2:C5, C2:C5, ">100", C2:C5, "<300")

Deze formule specificeert eerst het celbereik om op te tellen (C2:C5) als eerste argument. Vervolgens past hij twee bovenstaande voorwaarden toe op hetzelfde bereik. De functie SUMIFS() evalueert elke cel in het bereik tegelijk aan de hand van beide criteria. En je ziet dat alleen cellen met waarden tussen 100 en 300 (exclusief) in de som worden opgenomen.

Tel de waarden binnen een getalbereik op met SUMIFS(). Afbeelding door auteur.

Probleemoplossing als SUMIFS() niet werkt

Als je SUMIFS()-functie niet werkt zoals verwacht, controleer dan het volgende:

Niet-overeenkomende bereikgroottes

Als je SUMIFS()-formule een fout teruggeeft, kan dat komen door bereikgroottes die niet overeenkomen. In SUMIFS() moeten alle door jou geselecteerde bereiken, zoals het sum_range en de criteria_ranges, hetzelfde aantal rijen en kolommen hebben.

In dit voorbeeld is het som-bereik B2:B8, maar het criteria-bereik is A2:A7, dat één rij minder heeft:

=SUMIFS(B2:B8, A2:A7, "Drake White")

Deze mismatch geeft een #VALUE!-fout. Zorg er dus voor dat alle bereiken even groot zijn, zoals dit:

=SUMIFS(B2:B8, A2:A8, "John Doe")

Onjuiste opmaak van tekstcriteria

In SUMIFS() moet je tekstcriteria tussen aanhalingstekens zetten. Als je dat niet doet, kan dat onjuiste resultaten opleveren. Deze formule geeft bijvoorbeeld 0 omdat Excel Drake White ziet als een naam of variabele, niet als tekst: 

=SUMIFS(B2:B8, A2:A8, Drake White)

Om dit op te lossen, zet Drake White tussen aanhalingstekens zoals hier, dan krijg je de juiste uitvoer:

=SUMIFS(B2:B8, A2:A8, "Drake White")

Onverwachte nulwaarden

Als de formule 0 teruggeeft terwijl je een resultaat verwacht, kan dat komen door onjuiste opmaak van numerieke of datumvoorwaarden. In SUMIFS() moeten voorwaarden zoals >100 of <01-Jan-2023 als tekststrings tussen aanhalingstekens worden geschreven. Anders krijg je een fout of 0 omdat de voorwaarde niet tussen aanhalingstekens staat.

Of we kunnen de datum in een aparte cel opslaan (bijv. C1) en dit gebruiken:

=SUMIFS(B2:B8, A2:A8, ">="&C1)

SUMIFS() werkt niet met matrixtransformaties

SUMIFS() ondersteunt bepaalde transformaties niet, zoals het binnen de formule extraheren van specifieke componenten uit gegevens. Als ik bijvoorbeeld de verkopen van januari en maart wil optellen, werkt SUMIFS() niet rechtstreeks met matrixcriteria {"January","March"}.

Om dit te laten zien: stel dat ik maanden in kolom A heb (January, February, March, April) en verkoopwaarden in kolom B (100, 200, 300, 400). De formule

=SUMIFS(B2:B5, A2:A5, {"January","March"})

geeft niet de verwachte som van 400 (100 + 300). In plaats daarvan kunnen we SUMPRODUCT() gebruiken:

=SUMPRODUCT((A2:A5={"January","March"})*B2:B5)

of SUMIFS() omhullen met SUM() met deze formule:

=SUM(SUMIFS(B2:B5, A2:A5, {"January","March"}))

Beide oplossingen verwerken de matrixcriteria correct en geven de som van de verkopen voor januari en maart terug.

Best practices voor het gebruik van SUMIFS()

Om het meeste uit de functie SUMIFS() te halen, moeten we veelgemaakte fouten vermijden door deze best practices te volgen:

• Gebruik jokertekens voor flexibele filtering: Het is aan te raden jokertekens te gebruiken bij het filteren van data, omdat onze data soms niet perfect georganiseerd is. Productnamen zijn misschien niet altijd hetzelfde gespeld of er staan extra spaties. Jokertekens helpen je patronen te matchen zonder exacte tekst nodig te hebben.

• Vermijd het hardcoderen van criteria: Sla voorwaarden op in een aparte cel in plaats van ze direct in je formule te typen. Als je een voorwaarde zoals "Drake White" of ">100" hardcodeert, moet je de formule elke keer aanpassen als je iets wilt wijzigen. Met celverwijzingen wordt de formule automatisch bijgewerkt.

• Gebruik absolute verwijzingen bij het kopiëren van formules: Wanneer je een SUMIFS()-formule naar andere cellen kopieert, past Excel de celverwijzingen automatisch aan. Als je dat niet wilt, gebruik dan absolute verwijzingen door $ toe te voegen om de cellen vast te zetten.

Tot slot

SUMIFS() is een Excel-functie die ons helpt data te analyseren door te filteren en getallen op te tellen die aan specifieke criteria voldoen. Het is ideaal voor taken zoals verkopen per regio bijhouden, uitgaven per categorie samenvatten of voorraad tellen per producttype. Als je eenmaal vertrouwd bent met SUMIFS(), merk je dat het tijd bespaart en helpt om belangrijke trends in je data te zien zonder ingewikkelde omwegen.

Wil je deze skills verder uitbouwen? Bekijk dan onze cursus Data Analysis in Excel, de cursus Data Preparation in Excel en de cursus Data Visualization in Excel of de skill track Excel Fundamentals. Die helpen je je bestaande vaardigheden op te frissen.