Alle versiesVoorbeelden vragen Excel 2021 of nieuwerWiskundig

SUBTOTAAL

SUBTOTAL in Engelstalige Excel

Berekent een totaal, aantal, gemiddelde of andere samenvatting over alleen de zichtbare rijen van een lijst, en slaat andere subtotalen daarbij over.

Moeilijkheidsgraad

Laatst bijgewerkt: 13 oktober 2026

Bekijk de uitgewerkte voorbeelden

ABC
1Zichtbaar totaal€28,250.00€ 28.250,00=SUBTOTAAL(9, C4:C10)=SUBTOTAAL(9; C4:C10)
2
3BoekstukDagboekDebet
425-0401VRK€12,400.00€ 12.400,00
525-0402MEM€8,500.00€ 8.500,00
625-0403INK€3,150.00€ 3.150,00
725-0404MEM€15,000.00€ 15.000,00
825-0405BNK€2,275.00€ 2.275,00
925-0406MEM€4,750.00€ 4.750,00
1025-0407VRK€9,800.00€ 9.800,00
Opmerking: Een filter op Dagboek staat op MEM, dus de rijen 4, 6, 8 en 10 zijn verborgen. Alleen de rijen 5, 7 en 9 staan op het scherm.

Resultaat: €28,250.00€ 28.250,00

=SUBTOTAAL(9, C4:C10)
=SUBTOTAAL(9; C4:C10)

Wat SUBTOTAAL doet en wanneer je het gebruikt

Wat het doet: het telt een lijst op (of berekent het gemiddelde, telt aantallen, enzovoort) en slaat de rijen over die een filter verbergt, bijvoorbeeld het totaal van alleen de handmatige boekingen die het filter toont.

Wanneer je het gebruikt: onder een lijst waarop gefilterd wordt, en voor het eindtotaal van een rapport met subtotalen per sectie. Een code kiest de berekening: 9 telt op, 1 berekent het gemiddelde, 2 telt de getallen.

De drie grootste voordelen:

  • Volgt het filter: rijen die een filter verbergt, vallen uit het totaal; SOM telt ze wel mee.
  • Geen dubbeltelling: cellen in het bereik die zelf een SUBTOTAAL bevatten, worden overgeslagen, dus sectiesubtotalen en eindtotaal overlappen niet.
  • Zelf verborgen, jouw keuze: de codes 101 tot en met 111 slaan ook rijen over die je zelf hebt verborgen.

Hoe Excel de functie berekent

Excel leest eerst functie_getal en kiest daarmee de berekening: de laatste een of twee cijfers bepalen de functie, en vanaf 100 slaat Excel ook rijen over die je met de hand hebt verborgen.

Daarna gaat het verw1 rij voor rij langs. Een rij die door een filter is verborgen, valt altijd af. Een cel met een SUBTOTAAL-formule valt in alle gevallen af, zodat sectietotalen nooit twee keer meetellen.

Met de cellen die overblijven, voert Excel de gekozen berekening uit volgens de gewone regels van die functie; SOM negeert bijvoorbeeld tekst.

Let op: Een rij die je met Verbergen of met een samengevouwen groep hebt verborgen, valt alleen af bij de codes 101 tot en met 111; bij de codes 1 tot en met 11 telt die rij nog mee.

Denkmodel

Een rekenmachine die alleen leest wat er op het scherm staat. Laat je bij een paar rijen de rolluiken zakken, dan ziet hij die niet meer, en een regel waar al 'subtotaal' op staat, slaat hij over, want die getallen heeft hij al geteld.

Begrippen op deze pagina: bereik, cel

Syntaxis

Officiële documentatie van SUBTOTAAL
SUBTOTAAL(functie_getal, verw1, [verw2], ...)
SUBTOTAAL(functie_getal; verw1; [verw2]; ...)
ArgumentSoortBetekenis
functie_getalVerplichtWelke berekening: 9 = som, 1 = gemiddelde; +100 slaat ook zelf verborgen rijen over.
verw1VerplichtDe cellen die je wilt samenvatten, meestal één kolom van een lijst.
verw2OptioneelNog meer bereiken om mee te nemen, tot 254.

Geeft terug:Een getal: de gekozen berekening over de zichtbare rijen of over alle rijen

Formules gebruiken komma's (,) tussen de argumenten, zoals in de Engelse Excel. Formules gebruiken puntkomma's (;) tussen de argumenten, zoals in de Nederlandse Excel. Formules gebruiken puntkomma's (;) tussen de argumenten, zoals in de Nederlandse Excel; de Engelse Excel gebruikt komma's (,).

Uitgewerkte voorbeelden

Voorbeeldwerkmap downloaden(Excelbestand, 5 werkbladen, 18 kB)

Beginner

Totaal van de handmatige boekingen die het filter toont

Je hebt de XAF-auditfile van Veldhuis Installatie BV naar een werkblad geëxporteerd en een filter gezet op de kolom Dagboek. Je filtert op MEM om de handmatige boekingen te bekijken, en het totaal bovenaan moet alleen de MEM-regels optellen, niet alle journaalregels uit de export.

ABCD
1Zichtbaar totaal€28,250.00€ 28.250,00=SUBTOTAAL(9, C4:C10)=SUBTOTAAL(9; C4:C10)
2
3BoekstukDagboekDebet
425-0401VRK€12,400.00€ 12.400,00
525-0402MEM€8,500.00€ 8.500,00
625-0403INK€3,150.00€ 3.150,00
725-0404MEM€15,000.00€ 15.000,00
825-0405BNK€2,275.00€ 2.275,00
925-0406MEM€4,750.00€ 4.750,00
1025-0407VRK€9,800.00€ 9.800,00
11
Formulecel (C1)InvoerVerborgen of weggefilterde rijOpmerking: Een filter op Dagboek staat op MEM, dus de rijen 4, 6, 8 en 10 zijn verborgen. Alleen de rijen 5, 7 en 9 staan op het scherm.

Formule in C1

=SUBTOTAAL(9, C4:C10)
=SUBTOTAAL(9; C4:C10)

Resultaat: €28,250.00€ 28.250,00

Stap voor stap
  1. Excel leest functie_getal 9, en dat betekent SOM.
  2. Excel loopt C4:C10 rij voor rij af. De rijen 4, 6, 8 en 10 zijn door het filter verborgen, dus C4 (12400, VRK), C6 (3150, INK), C8 (2275, BNK) en C10 (9800, VRK) doen niet mee.
  3. Zichtbaar zijn C5, C7 en C9, met 8500, 15000 en 4750.
  4. Die worden opgeteld: 8500 + 15000 + 4750 = 28250.
  5. Wis het filter en dezelfde formule telt alle journaalregels op: 12400 + 8500 + 3150 + 15000 + 2275 + 4750 + 9800 = 55875. Een SOM(C4:C10) geeft in beide situaties 55875.

Let op: Zet je de formule in C11, direct onder de lijst, dan kan Excel die rij bij het filterbereik trekken: filter je op VRK, dan kan de totaalrij samen met de opgetelde rijen verdwijnen.

Meer over dit voorbeeld: waarom het werkt

Waarom het werkt

SUBTOTAAL kijkt bij elke rij eerst of die zichtbaar is en telt pas dan op. Een filter verbergt rijen, dus het totaal beweegt mee met het filter zonder dat je de formule aanraakt. Het totaal staat boven de lijst, in rij 1, en daar kan een filter op rij 3 niet bij.

Halfgevorderd

Kostenrekeningen in scope, zonder de rijen die je zelf verborg

Uit de saldibalans van Veldhuis Installatie BV heb je zes kostenrekeningen gehaald. De kostenspecificatie van de klant bevat er maar vier van, dus heb je de rekeningen 4510 en 4650 met rechtsklikken en Verbergen verborgen in plaats van verwijderd, want je moet ze nog apart toelichten. Het totaal bovenaan moet het bedrag in scope zijn, klaar om met de specificatie te vergelijken.

ABCD
1Bedrag in scope€678,550.00€ 678.550,00=SUBTOTAAL(109, C4:C9)=SUBTOTAAL(109; C4:C9)
2
3GrootboekrekeningNaamSaldo
440004000Personeelskosten€482,600.00€ 482.600,00
545004500Huisvestingskosten€96,400.00€ 96.400,00
645104510Energie€18,750.00€ 18.750,00
746004600Autokosten€41,300.00€ 41.300,00
846504650Reiskosten€12,900.00€ 12.900,00
948004800Afschrijvingen€58,250.00€ 58.250,00
10
Formulecel (C1)InvoerVerborgen of weggefilterde rijOpmerking: Rij 6 (rekening 4510) en rij 8 (rekening 4650) zijn verborgen met rechtsklikken en Verbergen. Er staat geen filter op dit werkblad.

Formule in C1

=SUBTOTAAL(109, C4:C9)
=SUBTOTAAL(109; C4:C9)

Resultaat: €678,550.00€ 678.550,00

Stap voor stap
  1. Excel leest functie_getal 109: de 9 aan het eind kiest SOM, en de 100 zorgt dat rijen die je met de hand hebt verborgen worden overgeslagen.
  2. Excel loopt C4:C9 af. Rij 6 (18750, energie) en rij 8 (12900, reiskosten) zijn verborgen en doen dus niet mee.
  3. Zichtbaar zijn 482600, 96400, 41300 en 58250, en die worden opgeteld: 482600 + 96400 + 41300 + 58250 = 678550.
  4. Met functie_getal 9 in plaats van 109 zouden de verborgen rijen wel meetellen: 678550 + 18750 + 12900 = 710200. De 9 laat alleen rijen weg die een filter verbergt, en er staat hier geen filter aan.
  5. Maak je rij 6 weer zichtbaar omdat rekening 4510 toch in scope blijkt te zijn, dan wordt C1 vanzelf 678550 + 18750 = 697300.

Let op: Een rij waarvan je de hoogte heel klein hebt gemaakt, lijkt verborgen maar is het niet, en SUBTOTAAL(109) telt hem nog steeds mee. Verberg rijen met Verbergen, niet door de rijrand omhoog te slepen.

Meer over dit voorbeeld: waarom het werkt, nog 1 valkuil, 1 variant

Waarom het werkt

Excel maakt onderscheid tussen rijen die een filter verbergt en rijen die je zelf verbergt, en functie_getal bepaalt of SUBTOTAAL ook die tweede soort overslaat. De 100-reeks slaat beide over en geeft dus hetzelfde antwoord, hoe de rijen ook verborgen zijn. Voor totalen die een beoordelaar leest, is dat de veiligste keuze.

Meer om op te letten

  • Een beoordelaar die je bestand opent, ziet 678550 en een lijst die compleet lijkt, terwijl de verborgen rekeningen uit beeld zijn. Zet het aantal verborgen rekeningen naast het totaal, bijvoorbeeld met SUBTOTAAL(3; A4:A9) min SUBTOTAAL(103; A4:A9), en dat geeft 6 - 4 = 2.

Varianten

=SUBTOTAAL(103, A4:A9)
=SUBTOTAAL(103; A4:A9)

Telt de zichtbare rekeningen, 4, met de code voor AANTALARG; handig als toelichting naast het totaal.

Gevorderd

Alleen zichtbare handmatige boekingen

De journaalregels van Veldhuis Installatie BV zijn gefilterd op periode 11 en 12, de periodes rond het jaareinde. Je wilt het zichtbare bedrag van alleen de MEM-boekingen (handmatig) weten.

SOMMEN.ALS trekt zich niets van het filter aan en SUBTOTAAL kent geen voorwaarde. Daarom laat je SUBTOTAAL per rij bepalen of die zichtbaar is, en past SOMPRODUCT de voorwaarde op het dagboek toe.

ABCD
1DagboekMEM
2Zichtbaar bedrag€30,450.00€ 30.450,00=SOMPRODUCT(SUBTOTAAL(109, VERSCHUIVING(C5, RIJ(C5:C11) - RIJ(C5), 0)) * (B5:B11 = B1))=SOMPRODUCT(SUBTOTAAL(109; VERSCHUIVING(C5; RIJ(C5:C11) - RIJ(C5); 0)) * (B5:B11 = B1))
3
4PeriodeDagboekBedrag
51111MEM€6,200.00€ 6.200,00
61212VRK€18,400.00€ 18.400,00
71212MEM€9,750.00€ 9.750,00
81010MEM€3,300.00€ 3.300,00
91111INK€5,100.00€ 5.100,00
101212MEM€14,500.00€ 14.500,00
111010VRK€7,600.00€ 7.600,00
12
Formulecel (B2)InvoerVerborgen of weggefilterde rijOpmerking: Een filter op Periode toont alleen periode 11 en 12, dus de rijen 8 en 11 (periode 10) zijn verborgen. Code 109 laat ze weg, en code 9 zou dat ook doen.

Formule in B2

=SOMPRODUCT(SUBTOTAAL(109, VERSCHUIVING(C5, RIJ(C5:C11) - RIJ(C5), 0)) * (B5:B11 = B1))
=SOMPRODUCT(SUBTOTAAL(109; VERSCHUIVING(C5; RIJ(C5:C11) - RIJ(C5); 0)) * (B5:B11 = B1))

Resultaat: €30,450.00€ 30.450,00

Stap voor stap
  1. Excel begint binnenin. RIJ(C5:C11) geeft 5 tot en met 11 en RIJ(C5) geeft 5, dus het verschil levert de verschuivingen 0, 1, 2, 3, 4, 5 en 6 op.
  2. VERSCHUIVING(C5; verschuivingen; 0) maakt van die zeven getallen zeven losse verwijzingen van één cel: C5, C6, C7, C8, C9, C10 en C11.
  3. SUBTOTAAL(109; ...) telt elke verwijzing van één cel apart op. Een zichtbare cel levert haar waarde op, een verborgen cel 0. Uitkomst: 6200, 18400, 9750, 0, 5100, 14500, 0. De nullen horen bij rij 8 en rij 11.
  4. B5:B11 = B1 vergelijkt elk dagboek met MEM: WAAR, ONWAAR, WAAR, WAAR, ONWAAR, WAAR, ONWAAR.
  5. Bij het vermenigvuldigen telt WAAR als 1 en ONWAAR als 0: 6200 × 1, 18400 × 0, 9750 × 1, 0 × 1, 5100 × 0, 14500 × 1, 0 × 0 geeft 6200, 0, 9750, 0, 0, 14500, 0.
  6. SOMPRODUCT telt de matrix op: 6200 + 9750 + 14500 = 30450. De MEM-regel van periode 10 (3300) telt niet mee, omdat die rij verborgen is.
  7. Wis het filter en het resultaat wordt 6200 + 9750 + 3300 + 14500 = 33750, hetzelfde als SOMMEN.ALS(C5:C11; B5:B11; B1).

Let op: Met SOMMEN.ALS krijg je hier 33750, terwijl je op het scherm alleen periode 11 en 12 ziet. Er lijkt niets mis, maar in dat bedrag zit een verborgen rij.

Meer over dit voorbeeld: waarom het werkt, nog 2 valkuilen, 1 variant

Waarom het werkt

SUBTOTAAL kan zelf geen voorwaarde toepassen, maar geef je de functie één cel, dan krijg je de waarde terug als de rij zichtbaar is en 0 als die verborgen is. Zo werkt SUBTOTAAL als zichtbaarheidsvlag. VERSCHUIVING levert elke rij aan als losse verwijzing van één cel, en SOMPRODUCT combineert die vlaggen met de test op het dagboek en telt alles op, zonder Ctrl+Shift+Enter.

Meer om op te letten

  • VERSCHUIVING is een vluchtige functie: bij elke wijziging in de werkmap rekent Excel hem opnieuw uit. Bij een auditfile-export met honderdduizenden regels wordt het bestand met deze opbouw merkbaar trager.
  • Beginnen het anker C5 en het bereik C5:C11 niet op dezelfde rij, dan schuiven alle vlaggen een rij op en haalt het resultaat ongemerkt journaalregels door elkaar.

Varianten

=SOMMEN.ALS(C5:C11, B5:B11, B1)
=SOMMEN.ALS(C5:C11; B5:B11; B1)

De voorwaarde zonder rekening te houden met zichtbaarheid: 33750. Kies deze als het antwoord niet mag afhangen van wie het laatst een filter heeft gezet.

ExpertExcel 2021 of nieuwer

Gemiddeld openstaand bedrag per zichtbare regel in een ouderdomsanalyse

De ouderdomsanalyse debiteuren van Veldhuis Installatie BV is gemaakt met Gegevens > Subtotaal: elke ouderdomsklasse sluit af met een SUBTOTAAL-rij, en via het overzicht vouw je een klasse samen. Je vouwt de klasse ouder dan 90 dagen samen en wilt het gemiddelde openstaande bedrag per zichtbare debiteurenregel weten, zonder de klassetotalen en zonder de ingeklapte regels.

Ook als er geen enkele regel meer zichtbaar is, moet de formule blijven werken.

ABCD
1Gemiddelde per zichtbare regel€10,525.00€ 10.525,00=LET( bedragen, C4:C10, totaal, SUBTOTAAL(109, bedragen), regels, SUBTOTAAL(102, bedragen), ALS(regels = 0, 0, AFRONDEN(totaal / regels, 2)) )=LET( bedragen; C4:C10; totaal; SUBTOTAAL(109; bedragen); regels; SUBTOTAAL(102; bedragen); ALS(regels = 0; 0; AFRONDEN(totaal / regels; 2)) )
2
3OuderdomsklasseRelatieOpenstaand
40 tot 30 dagenD1001€12,400.00€ 12.400,00
50 tot 30 dagenD1002€8,650.00€ 8.650,00
6Totaal 0 tot 30 dagen€21,050.00€ 21.050,00
7Ouder dan 90 dagenD1003€15,200.00€ 15.200,00
8Ouder dan 90 dagenD1004€6,800.00€ 6.800,00
9Ouder dan 90 dagenD1005€3,950.00€ 3.950,00
10Totaal ouder dan 90 dagen€25,950.00€ 25.950,00
11
Formulecel (C1)InvoerVerborgen of weggefilterde rijOpmerking: C6 bevat =SUBTOTAAL(9; C4:C5) en C10 bevat =SUBTOTAAL(9; C7:C9). De klasse ouder dan 90 dagen is samengevouwen, waardoor de rijen 7 tot en met 9 verborgen zijn; het klassetotaal in rij 10 blijft zichtbaar.

Formule in C1

=LET(
  bedragen, C4:C10,
  totaal, SUBTOTAAL(109, bedragen),
  regels, SUBTOTAAL(102, bedragen),
  ALS(regels = 0, 0, AFRONDEN(totaal / regels, 2))
)
=LET(
  bedragen; C4:C10;
  totaal; SUBTOTAAL(109; bedragen);
  regels; SUBTOTAAL(102; bedragen);
  ALS(regels = 0; 0; AFRONDEN(totaal / regels; 2))
)

Resultaat: €10,525.00€ 10.525,00

Stap voor stap
  1. LET noemt het bereik C4:C10 bedragen. Het blijft een verwijzing, en dat moet ook: alleen zo weet SUBTOTAAL welke rijen zichtbaar zijn.
  2. totaal is SUBTOTAAL(109; bedragen). C6 en C10 bevatten SUBTOTAAL-formules en vallen af. De rijen 7 tot en met 9 zijn verborgen door de ingeklapte klasse en vallen af door de 100. Over blijven C4 en C5: 12400 + 8650 = 21050.
  3. regels is SUBTOTAAL(102; bedragen), de code voor AANTAL, met dezelfde regels voor wat wordt overgeslagen. Dat telt C4 en C5: 2.
  4. regels is geen 0, dus ALS berekent AFRONDEN(21050 / 2; 2) = 10525,00.
  5. Klap de klasse weer uit en de rijen komen terug: totaal = 12400 + 8650 + 15200 + 6800 + 3950 = 47000, regels = 5, en het resultaat is 47000 / 5 = 9400,00. De klassetotalen blijven buiten beschouwing.
  6. Vouw beide klassen samen en regels wordt 0. Zonder de ALS zou 0 / 0 #DEEL/0! opleveren; met de ALS staat er in C1 een 0.

Let op: De omgekeerde fout is 9 en 2 in plaats van 109 en 102. Een ingeklapte klasse is met de hand verborgen, niet weggefilterd, dus de uitkomst is 9400,00, of de klasse nu is ingeklapt of niet: een geloofwaardig getal dat geen rekening houdt met wat je ziet.

Meer over dit voorbeeld: waarom het werkt, nog 2 valkuilen, 1 variant

Waarom het werkt

Twee keer SUBTOTAAL met codes die bij elkaar horen (109 en 102) kijkt naar precies dezelfde cellen, dus de deling is een echt gemiddelde van wat je op het scherm ziet. Een kale GEMIDDELDE over C4:C10 zou beide klassetotalen en alle ingeklapte regels meenemen: (12400 + 8650 + 21050 + 15200 + 6800 + 3950 + 25950) / 7 = 13428,57.

Kunnen er foutwaarden in de kolom staan, gebruik deze opbouw dan niet, want SUBTOTAAL geeft de fout door. AGGREGAAT met functie 1 en optie 3 berekent het gemiddelde van de zichtbare rijen en slaat fouten en geneste subtotalen over, maar minder collega's kennen die functie.

Meer om op te letten

  • Meng je de codes, bijvoorbeeld 109 met 2, dan deel je het zichtbare totaal door een aantal waarin de verborgen rijen meetellen: 21050 / 5 = 4210,00.
  • Een klassetotaal dat als waarde is ingetypt of met SOM is gemaakt, is geen SUBTOTAAL. Het wordt dus niet meer overgeslagen en het gemiddelde schuift omhoog. Houd van elk klassetotaal een SUBTOTAAL-formule.

Varianten

=SUBTOTAAL(101, C4:C10)
=SUBTOTAAL(101; C4:C10)

Alleen de code voor GEMIDDELDE geeft hier ook 10525; de LET-versie vangt daarnaast een lege weergave op en houdt totaal en aantal apart beschikbaar, bijvoorbeeld voor een toelichting.

Veelgemaakte fouten

SymptoomOorzaakOplossing
Verborgen rijen tellen nog mee in het totaalRijen die je met rechtsklikken en Verbergen hebt verborgen, tellen nog mee, omdat in de formule 9 staat in plaats van 109.Gebruik hier 109, en in het algemeen de codes 101 tot en met 111.
Het totaal verdwijnt zodra je filtertHet totaal staat in een rij die het filter mee verbergt, dus het verdwijnt zodra iemand filtert.Zet het totaal boven de koprij, of houd een lege rij vrij tussen de lijst en het totaal, zodat het filterbereik daarvoor ophoudt.
Het eindtotaal telt elk bedrag twee keerJe maakt een eindtotaal met SOM over een kolom waarin ook subtotalen per sectie staan, en elk bedrag telt twee keer.Gebruik SUBTOTAAL voor de sectietotalen én voor het eindtotaal; de functie slaat cellen met een SUBTOTAAL-formule over.