Home › Excel-functies › SUBTOTAAL
SUBTOTAAL in Excel: rekenen met gefilterde gegevens
SUBTOTAAL berekent een som, aantal, gemiddelde of andere statistiek van een bereik, maar slaat rijen over die door een filter verborgen zijn. Filter je lijst op één regio en het totaal toont alleen die regio. In de Engelse Excel heet de functie SUBTOTAL.
Excel CSE · 3 min leestijdIn deze uitleg
Syntaxis van SUBTOTAAL
=SUBTOTAAL(functie_getal; verw1; [verw2]; …)| Argument | Betekenis |
|---|---|
| functie_getal | Een getal dat bepaalt welke berekening SUBTOTAAL doet, zoals 9 voor SOM of 109 voor SOM zonder verborgen rijen. Zie de tabel hieronder. |
| verw1, verw2, … | Het bereik of de bereiken waarover je rekent. |
Functienummers
| Berekening | Telt handmatig verborgen rijen mee | Slaat handmatig verborgen rijen over |
|---|---|---|
| GEMIDDELDE | 1 | 101 |
| AANTAL (getallen) | 2 | 102 |
| AANTALARG (niet leeg) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SOM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
1–11 of 101–111: beide versies slaan rijen over die door een filter verborgen zijn. Het verschil zit in rijen die je zelf verbergt (rechtsklik › Verbergen): 1–11 tellen die nog mee, 101–111 laten ze weg. Twijfel je, gebruik dan 101–111: die rekenen altijd alleen met wat je ziet.
Voorbeeld: een gefilterde lijst optellen
| A | B | C | |
|---|---|---|---|
| 1 | Regio | Verkoper | Omzet |
| 2 | Noord | Anna | 1.200 |
| 3 | Zuid | Ben | 800 |
| 4 | Noord | Chris | 950 |
| 5 | Oost | Dana | 450 |
| 6 | Zuid | Anna | 700 |
| 7 | Totaal | 4.100 |
Zonder filter toont C7 4.100, hetzelfde als SOM. Filter nu kolom A op "Noord" (Gegevens › Filter). Rij 3, 5 en 6 verdwijnen en het totaal wordt 2.150: alleen de zichtbare rijen tellen mee. Een gewone =SOM(C2:C6) zou 4.100 blijven tonen.
Zichtbare rijen tellen of middelen
- Zichtbare rijen tellen:
=SUBTOTAAL(103; A2:A6)telt de zichtbare niet-lege cellen. Na filteren op Noord geeft dit 2. - Gemiddelde van wat zichtbaar is:
=SUBTOTAAL(101; C2:C6)geeft 1.075 voor Noord. - Hoogste zichtbare waarde:
=SUBTOTAAL(104; C2:C6)geeft 1.200 voor Noord.
Tip: zet de SUBTOTAAL-formules boven de lijst in plaats van eronder. Dan blijven ze zichtbaar als de lijst lang wordt.
SUBTOTAAL slaat andere subtotalen over
Staan er in een bereik cellen die zelf SUBTOTAAL gebruiken, dan worden die overgeslagen. Je kunt dus subtotalen per groep maken en een eindtotaal over de hele kolom, zonder iets dubbel te tellen. Precies dat doet de ingebouwde opdracht Gegevens › Subtotaal: die voegt per groep een SUBTOTAAL-formule in en één voor het eindtotaal. Ook de totaalrij van een Excel-tabel (Ctrl+T, daarna Tabelontwerp › Totaalrij) gebruikt SUBTOTAAL.
Veelgemaakte fouten
- Het totaal verandert niet als je rijen handmatig verbergt. Je gebruikte 1–11. Stap over op 101–111.
- Verborgen kolommen tellen nog mee. SUBTOTAAL slaat alleen verborgen rijen over, geen verborgen kolommen in een horizontaal bereik.
- #WAARDE! Het functienummer is niet een van 1–11 of 101–111.
- Je wilt ook fouten negeren. Gebruik AGGREGAAT, dat werkt als SUBTOTAAL maar ook foutwaarden kan overslaan:
=AGGREGAAT(9; 7; C2:C6).
Veelgestelde vragen
Wat is het verschil tussen SUBTOTAAL 9 en 109?
Beide tellen alleen de rijen op die zichtbaar zijn na filteren. 109 slaat ook rijen over die je zelf hebt verborgen; 9 telt die nog mee.
Hoe tel ik alleen zichtbare cellen op in Excel?
Gebruik =SUBTOTAAL(109; bereik). Dat slaat rijen over die door een filter of handmatig verborgen zijn.
Waarom SUBTOTAAL in plaats van SOM?
SOM telt altijd elke cel in het bereik op. SUBTOTAAL houdt rekening met filters en slaat andere subtotalen over, dus je krijgt het juiste totaal van wat er op dat moment zichtbaar is.
Wat is AGGREGAAT?
AGGREGAAT is een krachtigere versie van SUBTOTAAL, beschikbaar sinds Excel 2010. Het heeft meer berekeningen en kan ook foutwaarden negeren.