Hoe sommeert u waarden in Excel zonder of exclusief subtotalen?
Bij het werken met datasets in Excel is het gebruikelijk om uw getallen te organiseren en analyseren door subtotalen te berekenen voor verschillende groepen of categorieën. Na het toevoegen van deze subtotalen wilt u echter mogelijk een eindtotaal berekenen dat deze subtotaalwaarden uitsluit, zodat dubbel tellen in uw uiteindelijke som wordt voorkomen. Het probleem is dat bij direct optellen van alle getallen de subtotalen worden meegerekend, wat resulteert in een totaalsom die groter is dan bedoeld. Om dit te vermijden, moet u een manier vinden om de oorspronkelijke gegevens op te tellen terwijl de subtotalenrijen worden uitgesloten. In dit artikel presenteren we verschillende praktische methoden om dit probleem op te lossen, zodat u efficiënt nauwkeurige resultaten kunt berekenen in verschillende scenario’s.
Sommige waarden zonder subtotalen met de SOM-functie in Excel
Een veelgebruikte methode om groepssubtotalen te berekenen, is het invoegen van de SOM-functie binnen of onder elke relevante groep. Wanneer u echter het eindtotaal van de volledige lijst wilt berekenen én de rijen met groepssubtotalen wilt negeren, is daarvoor vaak een extra stap nodig. Hieronder vindt u een methode om dit voor elkaar te krijgen:
1. Gebruik de SOM-functie binnen elke groep om de bijbehorende subtotalen te genereren, zoals weergegeven in deze schermafbeelding:
2. Voer vervolgens in een lege cel — op de plek waar u het resultaat wilt weergeven — de volgende formule in om het eindtotaal te krijgen, zonder de subtotalenrijen mee te rekenen:
=SOM(B2:B21)/2
Druk op Enter om het juiste totaal te verkrijgen. Deze formule werkt omdat ervan wordt uitgegaan dat u subtotalen met de SOM-functie direct na de relevante groepen hebt ingevoegd, waardoor de som van de ruwe gegevens effectief verdubbeld wordt. Door te delen door 2, verwijdert u de dubbele som. Let op: deze methode is het meest geschikt wanneer de dataset zodanig is gestructureerd dat de som van de oorspronkelijke waarden en subtotalen samen precies tweemaal de som van de oorspronkelijke gegevens vormt.
Als uw gegevensstructuur complexer is of het aantal subtotalenrijen varieert, bieden de alternatieve methoden hieronder een nauwkeurigere en flexibelere oplossing.
Somwaarde zonder subtotalen met de SUBTOTAAL-functie in Excel
De functie SUBTOTAAL in Excel biedt een ingebouwde oplossing om berekeningen uit te voeren op alleen gefilterde of zichtbare rijen. Daardoor is deze functie bijzonder geschikt voor lijsten met subtotalen: SUBTOTAAL-formules kunnen namelijk zo worden ingesteld dat ze andere SUBTOTAAL-resultaten binnen hetzelfde bereik negeren, waardoor dubbel tellen van waarden wordt voorkomen.
Volg deze stappen om de SUBTOTAAL-functie in deze context te gebruiken:
1. Voer de SUBTOTAAL-functie voor elke groep als volgt in:
=SUBTOTAAL(9;B2:B10)
Hiermee wordt de SOM-bewerking (functienummer 9) toegepast op het beperkte bereik en wordt deze vaak gebruikt als onderdeel van de ingebouwde functie Gegevens > Subtotaal in Excel.
2. Voer voor het eindtotaal dat interne subtotalen uitsluit de volgende formule in de gewenste cel in:
=SUBTOTAAL(9;B2:B21)
Druk op Enter om automatisch de gegevenswaarden op te tellen, waarbij geneste SUBTOTAAL-functies binnen het bereik worden genegeerd. Deze aanpak is ideaal voor lijsten die de SUBTOTAAL-functie gebruiken voor groepssamenvattingen en voorkomt dat subtotalen per ongeluk meerdere keren worden meegeteld.
Houd er rekening mee dat de SUBTOTAAL-functie optimaal werkt wanneer subtotalencellen zijn gegenereerd met SUBTOTAAL in plaats van SOM. Bovendien kunt u SUBTOTAAL zo instellen dat, bij gefilterde of verborgen rijen, alleen zichtbare (niet-verborgen) gegevens worden meegenomen in de berekening — ideaal voor dynamische rapportagesituaties.
VBA-code – Sommeer waarden exclusief subtotalenrijen
Als uw gegevensstructuur complex is of u een geautomatiseerde methode wilt om alleen de ruwe gegevensrijen (exclusief subtotalen) op te tellen, kunt u een eenvoudige VBA-macro gebruiken. Met deze aanpak kunt u programmatisch alleen die rijen identificeren en optellen die voldoen aan uw criteria, zoals op basis van specifieke opmaak, de aanwezigheid van formules of andere onderscheidende kenmerken.
Als uw subtotalenrijen bijvoorbeeld zijn berekend met formules (zoals SOM of SUBTOTAAL) en uw ruwe gegevensrijen alleen statische waarden bevatten, kunt u de VBA-code zo instellen dat alleen cellen in een opgegeven kolom die géén formules bevatten, worden meegenomen in de berekening. Zo werkt dat:
1. Klik op Ontwikkelaarshulpmiddelen > Visual Basic om het venster Microsoft Visual Basic for Applications te openen.
2. Klik in het nieuwe venster op Invoegen > Module en plak de volgende code in de module:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Klik op de knop 
Uitvoeren om de code uit te voeren. Er verschijnt een prompt waarin u het bereik met uw gegevens kunt selecteren. De macro telt vervolgens alleen de cellen in uw selectie die geen formules bevatten, waardoor typische subtotalenrijen effectief worden overgeslagen.
Filteren – Gebruik filter en SUBTOTAAL om alleen zichtbare (niet-subtotaal) rijen op te tellen
Als u subtotalenrijen in uw dataset kunt filteren—bijvoorbeeld op basis van een specifiek label, trefwoord of patroon—dan combineert u de ingebouwde filterfunctie van Excel met de SUBTOTAAL-functie om alleen de zichtbare (niet-gefilterde) gegevensrijen op te tellen. Deze aanpak is bijzonder handig wanneer subtotalen steeds dezelfde indicator of hetzelfde label bevatten.
1. Klik ergens in uw dataset, ga vervolgens naar het tabblad Gegevens en klik op Filter om filters voor uw kolommen in te schakelen.
2. Klik op de vervolgkeuzelijst in uw hulpkolom en schakel „Subtotaal” uit om subtotalenrijen te verbergen en alleen de oorspronkelijke gegevens weer te geven.
3. Voer in een afzonderlijke cel de volgende formule in om alleen zichtbare (gefilterde) rijen op te tellen.
=SUBTOTAL(9,B2:B21)De SUBTOTAAL-functie (met functienummer 9 voor SOM) negeert verborgen rijen en geeft het totaal van alleen de momenteel zichtbare rijen weer — oftewel de niet-subtotaalrijen die na filtering zijn overgebleven. Deze oplossing is snel én dynamisch: zodra u filters opnieuw toepast of uw dataset uitbreidt, wordt de formule automatisch bijgewerkt.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins