Hoe werkt Subtotaal in Excel?

Excel | Hoe werkt Subtotaal in Excel?

Ken jij de functie ‘Subtotaal‘? Deze functie lijkt een beetje op de ‘auto som‘ functie die regelmatig opduikt. De ‘Auto-som’ functie leer je volgens mij al als eerste op school. Wat de auto som functie doet kan de functie ‘subtotaal‘ eigenlijk net iets beter. Maar dan moet je wel even weten hoe deze werkt. Het mooie aan de SUBTOTAAL functie is dat je o.a. kan aangeven wat er precies moet worden gesommeerd, geteld of vermenigvuldigt. Dit doe je door een code mee te geven. Een stukje lager in dit artikel vind je de codes terug met bijhorende betekenis. De Subtotaal formule kan er zo als hieronder uit zien, wat dus betekend dat alleen de getoonde cellen opgeteld moeten worden.

=SUBTOTAAL(109;A2:A29)

Incl. verborgen velden Excl. verborgen velden Functie
1 101 GEMIDDELDE
2 102 AANTAL
3 103 AANTALARG
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SOM
10 110 VAR
11 111 VARP

 

In het overzicht zie je dat je met de code 9 en 109 kan sommeren. Met de code 9 sommeer je alle cellen en met code 109 alleen de zichtbare cellen. Dit is met name handig wanneer je ook gebruik maakt van een autofilter. Met functie 109 worden alleen de zichtbare cellen geteld en in combinatie met de auto filter betekend dit dus dat de subtotaal iedere keer anders kan zijn afhankelijk van waar je op filtert. Het tegenovergestelde is waar met code 9.

Het gemiddelde uitrekenen met Subtotaal

Zoals gezegd kan de Subtotaal functie ook het gemiddelde uitrekenen. Hiervoor gebruik je de code 1 of 101.

De code 101 zorgt er voor dat alleen de getoonde velden worden gebruikt in de calculatie. Code 1 neemt alle velden mee in de calculatie ongeacht of deze verborgen zijn of niet. In onderstaand voorbeeld zie je dat het gemiddelde goed wordt berekend op twee verschillende manieren.

Subtotaal-subtotal-excel-functie-101-gemiddelde-average

 

Het aantal cellen tellen met Subtotaal

Hetzelfde trucje kan de Subtotaal functie ook met het tellen van cellen. Hiervoor gebruik je de code 2 of 102.

De code 102 zorgt er voor dat alleen de getoonde velden worden gebruikt in de calculatie. Code 2 neemt alle velden mee in de calculatie ongeacht of deze verborgen zijn of niet. In onderstaand voorbeeld zie je dat het aantal cellen goed wordt berekend op twee verschillende manieren.

Subtotaal-subtotal-excel-functie-102-count-aantal

Het grootste getal vinden met Subtotaal

De Subtotaal functie kan ook het grootste getal uit een reeks halen. Hiervoor gebruik je de code 4 of 104.

De code 104 zorgt er voor dat alleen de getoonde velden worden gebruikt in de calculatie. Code 4 neemt alle velden mee in de calculatie ongeacht of deze verborgen zijn of niet. In onderstaand voorbeeld zie je dat het grootste getal wordt gevonden op twee verschillende manieren.

Subtotaal-subtotal-excel-functie-104-max

Het kleinste getal vinden met Subtotaal

De Subtotaal functie kan ook het kleinste getal uit een reeks halen. Hiervoor gebruik je de code 5 of 105.

De code 105 zorgt er voor dat alleen de getoonde velden worden gebruikt in de calculatie. Code 5 neemt alle velden mee in de calculatie ongeacht of deze verborgen zijn of niet. In onderstaand voorbeeld zie je dat het kleinste getal wordt gevonden op twee verschillende manieren.

Subtotaal-subtotal-excel-functie-103-min

Cellen vermenigvuldigen met Subtotaal

De Subtotaal functie kan ook de inhoud van de cellen uit een reeks vermenigvuldigen. Hiervoor gebruik je de code 6 of 106.

De code 106 zorgt er voor dat alleen de getoonde velden worden gebruikt in de calculatie. Code 6 neemt alle velden mee in de calculatie ongeacht of deze verborgen zijn of niet. In onderstaand voorbeeld zie je dat alle cellen op de juiste manier worden vermenigvuldigd. Ter controle ook hier op twee verschillende manieren.

Subtotaal-subtota-excel-functie-106-product

Cellen sommeren met Subtotaal

Misschien wel de meeste gebruikte functie van Subtotaal. Het sommeren van van cellen uit een reeks. Hiervoor gebruik je zoals verwacht, code 9 of 109.

De code 109 zorgt er voor dat alleen de getoonde velden worden gebruikt in de calculatie. Code 9 neemt alle velden mee in de calculatie ongeacht of deze verborgen zijn of niet. In onderstaand voorbeeld zie je dat de cellen juist worden opgeteld. Op twee verschillende manieren.

Subtotaal-subtotal-excel-functie-109-som-sum


3 Reacties

  1. Marc
    | Beantwoorden

    Als je via “Gegevens” naar Subtotaal gaat, gebruikt de functie Som automatisch nr. 9 , hoe kan je dat handig naar 109 omzetten?

    • Danny
      | Beantwoorden

      Hallo Marc, ik weet niet hoe je dat snel kunt omzetten behalve door de formule handmatig te corrigeren. Persoonlijk gebruik ik nooit de wizards van Excel.

  2. Ruben
    | Beantwoorden

    Deze formule tref ik aan:
    =SUBTOTAAL(109;[Saldo])
    Ik snap niet precies wat de het deel [Saldo] doet.
    Er staat in de kolom, bovenaan de reeks getallen die opgeteld moeten worden een cel met de titel Saldo.
    Betekent [Saldo] dan dat er gekeken moet worden naar het bereik tussen de subtotaalcel en de Saldocel?

Heb je nog opmerkingen of vragen? Je kunt ze in de box hieronder of op het forum kwijt.