SUBTOTAL()-formule in Excel: een complete rekenhandleiding

Excel-gebruikers hebben vaak flexibele manieren nodig om gegevens samen te vatten die reageren op filters of groeperingen. Traditionele functies zoals SUM(), AVERAGE() of COUNT() berekenen resultaten op basis van alle cellen in een bereik, ongeacht of sommige rijen verborgen of eruit gefilterd zijn. De functie SUBTOTAL() biedt een dynamisch alternatief dat zijn berekeningen automatisch aanpast op basis van wat er op dat moment zichtbaar is in je werkblad.

Als je nieuw bent in Excel en eerst een solide basis wilt leggen voordat je geavanceerde functies zoals SUBTOTAL() gaat verkennen, bekijk dan onze cursus Introduction to Excel, waarin essentiële vaardigheden aan bod komen, zoals navigeren door werkbladen, basisformules en opmaaktechnieken voor gegevens, zodat je klaar bent voor meer geavanceerd Excel-werk.

Wat is de functie SUBTOTAL() in Excel?

De functie SUBTOTAL() berekent aggregatiewaarden zoals som, gemiddelde, aantal en andere statistische bewerkingen op een dataset. Wat hem onderscheidt van standaard Excel-functies is hoe hij dynamisch werkt—de uitkomst verandert op basis van wat zichtbaar is of gefilterd. De functie kan zowel handmatig verborgen rijen als door filters verborgen rijen negeren, afhankelijk van het functienummer dat je opgeeft.

Wanneer je een filter toepast op je gegevens of bepaalde rijen handmatig verbergt, past SUBTOTAL() zijn berekening automatisch aan zodat alleen de zichtbare cellen worden meegenomen. Dit maakt het een uitstekende keuze voor het maken van samenvattingsrijen in gefilterde datasets, het bouwen van interactieve dashboards of het genereren van rapporten die moeten worden bijgewerkt op basis van gebruikersselecties.

De functie ondersteunt dezelfde statistische bewerkingen als de standaardfuncties van Excel—waaronder som, gemiddelde, aantal, maximum, minimum en nog enkele andere—maar verpakt ze in één contextbewuste functie die zich aanpast aan de huidige staat van je gegevens.

Syntaxis en ondersteunde bewerkingen

De functie SUBTOTAL() volgt een eenvoudige syntaxisstructuur:

=SUBTOTAL(function_num, ref1, [ref2], ...)

De eerste parameter, function_num, bepaalt zowel het type berekening als hoe de functie met verborgen rijen omgaat. De overige parameters (ref1, ref2, enz.) zijn de celbereiken die je in de berekening wilt opnemen. Je kunt maximaal 254 verschillende bereiken opgeven.

De functienummers vallen in twee duidelijke categorieën op basis van hoe ze met handmatig verborgen rijen omgaan:

Functienummers 1-11 nemen cellen uit handmatig verborgen rijen mee in hun berekeningen, terwijl functienummers 101-111 deze uitsluiten. Beide bereiken negeren echter altijd rijen die door filters zijn verborgen.

Zo berekent SUBTOTAL(9, A2:A10) bijvoorbeeld de som van A2:A10 inclusief handmatig verborgen rijen, terwijl SUBTOTAL(109, A2:A10) handmatig verborgen rijen uitsluit van de somberekening. In beide gevallen worden gefilterde rijen automatisch uitgesloten van het resultaat.

Praktisch voorbeeld van de functie SUBTOTAL()

Laten we zien hoe SUBTOTAL() werkt met een praktisch voorbeeld met verkoopgegevens van een elektronica- en meubelwinkel.

Ongeselecteerde dataset. Afbeelding door de auteur. 

Onze voorbeeldgegevens bevatten zeven producten in twee categorieën: vier Elektronica-artikelen (Laptop, Phone, Tablet, Monitor) en drie Meubels (Desk, Chair, Lamp). Zonder filters tonen zowel de formule SUBTOTAL(109,C2:C8) als de reguliere formule SUM(C2:C8) hetzelfde resultaat: 3525 (het totaal van alle verkopen).

Gefilterde dataset. Afbeelding door de auteur. 

Nu hebben we een filter toegepast om alleen Elektronica-producten te tonen, waarbij de drie Meubelrijen verborgen zijn. Let op het belangrijke verschil in onze rekenresultaten:

• SUBTOTAL-formule toont 3000 - past zich automatisch aan om alleen de zichtbare Elektronica-rijen op te tellen
• Reguliere SUM-formule toont nog steeds 3525 - en neemt de verborgen Meubelartikelen nog mee

Deze vergelijking laat het kernvoordeel van SUBTOTAL() zien: de functie reageert dynamisch op filters en wijzigingen in zichtbaarheid, terwijl traditionele functies zoals SUM() statisch blijven. Wanneer je gegevens filtert of rijen verbergt, herberekent SUBTOTAL() direct om alleen te weerspiegelen wat momenteel zichtbaar is, waardoor het onmisbaar is voor responsieve dashboards en interactieve rapporten.

Hoe SUBTOTAL() werkt met verborgen en gefilterde gegevens

De functie SUBTOTAL() gaat op specifieke manieren om met verschillende typen verborgen gegevens. Het is belangrijk dit gedrag te begrijpen bij het opstellen van je formules.

SUBTOTAL() met gefilterde rijen

Wanneer je een filter toepast op je dataset, sluit SUBTOTAL() altijd de weggefilterde rijen uit van de berekeningen, ongeacht welk functienummer je gebruikt. Dit gedrag is automatisch en consistent voor alle functienummers (zowel 1-11 als 101-111).

Als je bijvoorbeeld verkoopgegevens hebt van januari t/m december en een filter toepast om alleen Q1-maanden (januari, februari, maart) te tonen, berekent SUBTOTAL(9, B2:B13) de som alleen voor die drie zichtbare maanden, waarbij de weggefilterde Q2–Q4-gegevens worden genegeerd.

SUBTOTAL() met handmatig verborgen rijen

Handmatig verborgen rijen worden verschillend behandeld afhankelijk van je keuze van functienummer. Wanneer je met rechts op rijnummers klikt en "Hide" selecteert, verberg je die rijen handmatig.

Met functienummers 1-11 worden deze handmatig verborgen rijen meegenomen in berekeningen. Met functienummers 101-111 worden ze uitgesloten. Zo houd je controle over de vraag of verborgen gegevens je resultaten moeten beïnvloeden.

SUBTOTAL() met horizontale en verticale bereiken

De functie SUBTOTAL() werkt het best met verticale gegevensbereiken (kolommen met gegevens). Hoewel de functie ook met horizontale bereiken overweg kan, is het verbergingsgedrag geoptimaliseerd voor rijgebaseerde bewerkingen, omdat de filter- en rijverbergfuncties van Excel verticaal werken.

Wanneer je SUBTOTAL()-formules in hetzelfde bereik nestelt, slaat de functie automatisch andere SUBTOTAL()-resultaten over om dubbeltellingen te voorkomen. Dit is vooral handig bij het maken van hiërarchische samenvattingen of subtotalen binnen groepen, zodat je totalen elke afzonderlijke groepering nauwkeurig weergeven zonder duplicatie.

Geavanceerde toepassingen van de functie SUBTOTAL()

Een dynamische dropdown-samenvatting maken met SUBTOTAL()

Je kunt SUBTOTAL() combineren met de functie IF() om interactieve samenvattingen te maken die veranderen op basis van de selectie van de gebruiker. Deze aanpak gebruikt een keuzelijst om gebruikers te laten schakelen tussen verschillende berekeningstypen.

Maak voor een dynamische dropdown-samenvatting eerst een keuzelijst in cel D1 met opties zoals "Sum", "Average", "Count" en "Max".

Gebruik vervolgens de volgende formule in een andere cel (bijv. D2) om de berekening dynamisch aan te passen op basis van de selectie van de gebruiker:

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

Deze formule beoordeelt de selectie in de dropdown en past de overeenkomstige SUBTOTAL()-functie toe. Gebruikers kunnen wisselen tussen berekeningstypen zonder formules te bewerken, waardoor rapporten interactiever en gebruiksvriendelijker worden.

SUBTOTAL() in Excel-tabellen en -overzichten

Excel-tabellen gebruiken automatisch SUBTOTAL() in de Total Row-functie. Wanneer je je gegevensbereik omzet naar een Tabel (Ctrl+T) en de Total Row inschakelt, voegt Excel standaard SUBTOTAL(109, …) in voor somberekeningen. Deze Total Row werkt dynamisch bij wanneer rijen worden gefilterd en past de resultaten direct aan zodat alleen zichtbare gegevens worden weergegeven.

Je kunt het berekeningstype wijzigen door op de pijl-omlaag te klikken in een cel van de Total Row en verschillende opties te selecteren, zoals Count, Average, Max of Min. Excel werkt het functienummer automatisch bij terwijl de SUBTOTAL()-structuur behouden blijft.

De Overzicht-functie (Data > Subtotal) maakt ook gebruik van SUBTOTAL()-formules. Wanneer je gegevens groepeert en subtotalen invoegt, plaatst Excel automatisch SUBTOTAL()-functies bij elke groepsonderbreking. Dit creëert hiërarchische samenvattingen die correct reageren wanneer je overzichtsniveaus uitklapt of inklapt, omdat de functie geneste subtotalen negeert.

Veelvoorkomende valkuilen en beperkingen

Door de veelvoorkomende fouten en beperkingen van SUBTOTAL() te begrijpen, voorkom je rekenfouten en kies je de juiste aanpak voor je data-analysebehoeften.

• #VALUE!-fout met ongeldige functienummers: De meest voorkomende fout treedt op wanneer je een ongeldig functienummer gebruikt. SUBTOTAL() accepteert alleen functienummers 1-11 en 101-111. Nummers zoals 12, 50 of 200 geven een #VALUE!-fout. Controleer altijd of je functienummer overeenkomt met een geldige bewerking uit de ondersteunde reeksen.

• Gedrag van verborgen kolommen versus verborgen rijen: Hoewel SUBTOTAL() reageert op verborgen rijen op basis van je keuze van functienummer, geldt dit niet voor verborgen kolommen. De functie neemt gegevens uit verborgen kolommen in alle berekeningen mee, ongeacht of je functienummers 1-11 of 101-111 gebruikt. Deze beperking betekent dat je alternatieve aanpakken nodig hebt wanneer de zichtbaarheid van kolommen je analyse beïnvloedt.

• 3D-verwijzingen niet ondersteund: In tegenstelling tot veel Excel-functies kan SUBTOTAL() geen bereiken refereren over meerdere werkbladen. Formules zoals SUBTOTAL(109, Sheet1:Sheet3!A1:A10) geven fouten. Als workaround kun je eerst sommen of andere totalen afzonderlijk op elk blad berekenen met standaardfuncties zoals SUM(), en deze tussentijdse resultaten vervolgens consolideren met één SUBTOTAL op een overzichtsblad.

• Foutwaarden blijven in berekeningen: SUBTOTAL() negeert geen cellen met foutwaarden zoals #N/A, #DIV/0! of #VALUE!. Deze fouten werken door in je subtotalen en kunnen hele resultaten ongeldig maken. Reinig je gegevens van fouten voordat je SUBTOTAL() toepast, of overweeg de functie AGGREGATE(), die foutwaarden kan overslaan.

• Verticale gegevensoriëntatie werkt het best: Hoewel SUBTOTAL() met horizontale bereiken kan werken, is de functie ontworpen voor verticale datastructuren. De filter-, sorteer- en verbergfuncties van Excel werken rij voor rij, waardoor verticale opstellingen beter aansluiten bij het beoogde gedrag van de functie.

Subtotal() vs. AGGREGATE()

De functie AGGREGATE() is een uitgebreid alternatief voor SUBTOTAL() met extra mogelijkheden voor het omgaan met fouten en meer statistische bewerkingen. Terwijl SUBTOTAL() 11 basisbewerkingen biedt, levert AGGREGATE() 19 verschillende functies, waaronder percentielen, kwartielen en mediaanberekeningen.

Het belangrijkste voordeel van AGGREGATE() zit in de mogelijkheden voor foutafhandeling. In tegenstelling tot SUBTOTAL(), dat foutwaarden in berekeningen opneemt, kan AGGREGATE() cellen met fouten zoals #N/A, #DIV/0! of #VALUE! automatisch overslaan. Dit is vooral handig voor datasets met onvolledige of problematische gegevens.

AGGREGATE() biedt ook fijnmazigere controle over wat moet worden genegeerd. Je kunt instellen dat verborgen rijen, geneste subtotalen, foutwaarden of elke combinatie daarvan worden overgeslagen met behulp van de opties-parameter.

Pro-tips bij het gebruik van SUBTOTAL()

Sorteer altijd eerst

Wanneer je de ingebouwde functie Data > Subtotal van Excel gebruikt, sorteer je gegevens dan eerst op de groepeer-kolom. Zo zorg je dat alle gerelateerde records bij elkaar staan en ontstaan er nette groepsonderbrekingen voor je subtotalen. Ongeordende gegevens leveren versnipperde subtotalen op die geen betekenisvolle samenvattingen bieden.

Gebruik labels in de eerste rij van kolommen

Plaats beschrijvende koppen in de eerste rij van je gegevensbereik voordat je subtotalen toepast. Excel gebruikt deze labels om betekenisvolle subtotalebeschrijvingen te maken en je rapporten beter leesbaar te maken. Duidelijke kolomkoppen helpen ook bij het selecteren van bereiken voor handmatige SUBTOTAL()-formules.

Let op de plaatsing van de samenvatting (erboven of eronder)

De Subtotal-tool van Excel plaatst standaard samenvattingsrijen onder elke groep, maar je kunt ervoor kiezen ze erboven te plaatsen. Denk bij deze keuze aan het beoogde gebruik van je rapport. Samenvattingsrijen boven groepen werken goed voor executive dashboards, terwijl samenvattingsrijen onder groepen aansluiten bij traditionele boekhoudformaten.

Rijen verbergen versus filteren voor precieze controle

Begrijp het verschil tussen filteren en rijen handmatig verbergen om de verwachte resultaten te krijgen. Gebruik filters wanneer je wilt dat SUBTOTAL() bepaalde gegevens tijdelijk negeert. Gebruik handmatig verbergen (rechtsklik > Hide) als je meer permanente uitsluitingen wilt, en kies vervolgens functienummers 101-111 om de verborgen status te respecteren.

Deze aanpak geeft je gelaagde controle: filter voor tijdelijke datweergaven, verberg handmatig voor semi-permanente uitsluitingen en selecteer passende functienummers om je verbergintenties te honoreren.

Conclusie

Leren om SUBTOTAL() effectief te gebruiken verandert hoe je data-analyse in Excel aanpakt. In plaats van statische berekeningen die stuklopen wanneer gegevens veranderen, kun je robuuste formules bouwen die zich automatisch aanpassen aan gebruikersacties en gegevenswijzigingen. Deze vaardigheid wordt vooral waardevol bij grote datasets of bij het maken van rapporten die meerdere gebruikers in de loop van de tijd zullen filteren en bewerken.

Om je Excel-expertise verder uit te bouwen dan losse functies zoals SUBTOTAL(), bekijk onze Excel Fundamentals skill track: een gestructureerd leerpad van 16 uur dat je meeneemt van gegevensvoorbereiding tot geavanceerde analyse- en visualisatietechnieken. Voor lezers die meteen analytische toepassingen willen verkennen, leert onze cursus Data Analysis in Excel je alles over draaitabellen en geavanceerde logische functies voor diepere inzichten.