Hoe cellen in een kolom uitsluiten van de som in Excel?
Bij het werken met gegevens in Excel, kom je vaak in de situatie dat je de som van een bereik wilt berekenen terwijl je bepaalde cellen bewust overslaat – bijvoorbeeld als die cellen uitschieters, fouten, irrelevante gegevens of specifieke waarden bevatten die je buiten beschouwing wilt laten. Zoals te zien is in de onderstaande schermafbeelding, stel dat je een kolom met getallen hebt, maar je wilt alles optellen behalve de waarden in cel A3 en A4. Dit artikel zal uitgebreid verschillende praktische methoden demonstreren om een bereik in Excel op te tellen terwijl je één of meer gespecificeerde cellen uitsluit, zodat je precies de resultaten krijgt die je nodig hebt voor financiële analyses, rapportages of inventarisbeheertaken waarvoor selectieve berekeningen vereist zijn.
- Cellen in een kolom uitsluiten van de som met formules
- VBA-code – Programmeer een som van een bereik, waarbij gespecificeerde cellen worden overgeslagen/uitgesloten
- Excel-formule – Gebruik SOM.ALS/SOMMEN.ALS om alleen waarden op te nemen die niet voldoen aan de uitsluitingscriteria
- Excel-formule – Gebruik de FILTER-functie in nieuwere versies van Excel om uitgesloten cellen te filteren voordat je ze optelt
Cellen in een kolom uitsluiten van de som met formules
Door eenvoudige rekenkunde binnen de SOM-formule te gebruiken, kun je ongewenste cellen direct uitsluiten in je berekening. Deze aanpak is geschikt voor snelle berekeningen wanneer je een klein aantal uitsluitingen moet verwerken. Volg deze stappen:
1. Selecteer een lege cel om het somresultaat weer te geven en voer de volgende formule in de formulebalk in, druk vervolgens op Enter om de som te berekenen terwijl je specifieke cellen uitsluit. Bijvoorbeeld:
=SOM(A2:A7)-SOM(A3:A4)
Uitleg & Tips:
- De SOM(A2:A7) berekent het hele bereik, terwijl SOM(A3:A4) de waarden van de uitgesloten cellen aftrekt. Dit werkt het beste wanneer de cellen die moeten worden uitgesloten aaneengesloten zijn.
- Je kunt gemakkelijk meerdere uitgesloten cellen combineren en aftrekken als ze niet naast elkaar liggen. Als je bijvoorbeeld A3 en A6 wilt uitsluiten uit het bereik, pas je de formule als volgt aan:
=SOM(A2:A7)-A3-A6
- Als de uitsluitingen verspreid of talrijk zijn, kan handmatig elke uitgesloten cel op te sommen de formules langer en moeilijker te beheren maken.
- Wees voorzichtig met celverwijzingen: als je gegevens of bereik verandert, werk dan de formule dienovereenkomstig bij om fouten te voorkomen.
VBA-code – Programmeer een som van een bereik, waarbij gespecificeerde cellen worden overgeslagen/uitgesloten
Voor scenario's waarin je veel uitsluitingen hebt of het proces vaak moet herhalen, biedt het gebruik van een VBA-macro flexibiliteit en automatisering. Met VBA kun je een gespecificeerd bereik optellen en elk aantal cellen uitsluiten, of ze nu aaneengesloten of niet-aaneengesloten zijn, door ze programmatisch te definiëren. Deze methode is geschikt voor gebruikers die bekend zijn met de VBA-omgeving en die complexere uitsluitingslogica willen stroomlijnen.
Waarschuwingen: VBA-macros kunnen je werkmap wijzigen. Sla altijd je werk op voordat je nieuwe code uitvoert. Macros moeten zijn ingeschakeld om bovenstaande te laten werken.
1. Ga naar Ontwikkelaarshulpmiddelen > Visual Basic om de VBA-editor te openen. Klik in het Projectvenster met de rechtermuisknop op je werkmap, selecteer Invoegen > Module en plak de volgende code in de module:
Sub SumWithExclusions()
Dim sumRange As Range
Dim excludeCells As Range
Dim cell As Range
Dim result As Double
Dim xTitleId
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set sumRange = Application.InputBox("Select the range to sum", xTitleId, Type:=8)
Set excludeCells = Application.InputBox("Select cells to exclude (use Ctrl+Click to select multiple)", xTitleId, Type:=8)
result = 0
If Not sumRange Is Nothing Then
For Each cell In sumRange
If Not Application.Intersect(cell, excludeCells) Is Nothing Then
' Skip excluded cells
Else
result = result + cell.Value
End If
Next
MsgBox "The sum excluding specified cells is: " & result, vbInformation
Else
MsgBox "No range selected.", vbExclamation
End If
End Sub2. Klik 
Uitvoeren in het VBA-venster of druk op F5 om de macro uit te voeren. Er verschijnt een dialoogvenster waarin je wordt gevraagd het volledige bereik te selecteren om op te tellen, en vervolgens de cellen die je wilt uitsluiten (houd Ctrl ingedrukt om meerdere te selecteren). De macro toont het resultaat in een berichtenvenster.
- Als je per ongeluk cellen selecteert, voer de macro dan opnieuw uit. Het proces is interactief en flexibel.
- Macros zijn het best geschikt voor routine taken of uitsluitingen op basis van complexe criteria.
Excel-formule – Gebruik SOM.ALS of SOMMEN.ALS om alleen waarden op te nemen die niet voldoen aan de uitsluitingscriteria
Voor meer geavanceerde of logica-gebaseerde uitsluitingen kun je de functies SOM.ALS of SOMMEN.ALS gebruiken. Deze formules werken goed wanneer je uitsluitingen zijn gebaseerd op waarden, criteria, of wanneer je een lijst met waarden hebt die je wilt vermijden.
Voorbeeld – Uitsluiten op basis van een specifieke waarde
1. Als je A2:A7 wilt optellen, maar de waarde '16' wilt uitsluiten, voer dan de volgende formule in je doelcel in (bijvoorbeeld in cel B1):
=SUMIF(A2:A7,"<>16")Deze formule telt alle waarden in het bereik A2:A7 op, behalve diegene die gelijk zijn aan 16.
2. Druk na het typen van de formule op Enter. Je kunt de bereik/celverwijzingen kopiëren of aanpassen naar behoefte.
Voorbeeld – Alle cellen uitsluiten die overeenkomen met een celwaarde
Stel dat cel C1 de waarde bevat die je wilt uitsluiten van de som:
=SUMIF(A2:A7,"<>"&A3)Werk C1 bij indien nodig, en de formule sluit dynamisch alle overeenkomende waarden uit.
- Voor meerdere uitsluitingscriteria of meer complexe regels, overweeg dan SOMMEN.ALS te gebruiken in combinatie met hulpkolommen of matrices. Echter, SOM.ALS/SOMMEN.ALS werkt het beste wanneer uitsluitingen zijn gebaseerd op specifieke en consistente criteria, niet op willekeurige celposities.
- Als je bereik tekst of lege cellen bevat, negeert SOM.ALS deze automatisch; zorg ervoor dat dit het gewenste gedrag is.
Excel-formule – Gebruik de FILTER-functie (nieuwe Excel-versies) om uitgesloten cellen te filteren voordat je ze optelt
Als je Excel voor Microsoft 365 of Excel 2021 en later gebruikt, maakt de FILTER-functie dynamische en flexibele uitsluiting van cellen mogelijk voordat je SOM toepast. Dit is vooral handig voor grote datasets of variabele uitsluitingscriteria.
Voorbeeld – Specifieke waarden uitsluiten (bijv., 16 en 13)
1. Voer de volgende formule in je doelcel in (bijvoorbeeld B1):
=SUM(FILTER(A2:A7,(A2:A7<>16)*(A2:A7<>13)))Dit telt alle waarden in A2:A7 op, behalve diegene die gelijk zijn aan 16 en 13. De FILTER-functie creëert een array die alleen cellen bevat die niet gelijk zijn aan die waarden, en vervolgens telt SOM ze op.
2. Druk op Enter. De berekening wordt dynamisch bijgewerkt als de uitsluitingen of brongegevens veranderen.
- Om waarden dynamisch uit te sluiten op basis van een lijst (bijvoorbeeld, de uitsluitingslijst staat in C2:C4):
=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))Deze formule sluit elke waarde in A2:A7 uit die overeenkomt met een waarde in C2:C4. Werk je uitsluitingslijst eenvoudigweg bij in kolom C, en de formule-resultaten worden automatisch bijgewerkt.
- De FILTER-gebaseerde aanpak wordt aanbevolen voor gebruikers die met de nieuwste Excel-versies werken en dynamische, schaalbare uitsluitingslogica zoeken.
- Als je een #CALC!-fout ontvangt, controleer dan of er na alle uitsluitingen ten minste één waarde overblijft in het bereik; anders retourneert FILTER een fout.
Samenvattend biedt Excel verschillende praktische oplossingen om een bereik op te tellen terwijl specifieke cellen of waarden worden uitgesloten. Eenvoudige formules zijn geschikt voor snelle, kleine uitsluitingen, terwijl SOM.ALS/SOMMEN.ALS en FILTER meer flexibele, voorwaarden-gestuurde scenario's ondersteunen. VBA is ideaal wanneer uitsluitingen talrijk, variabel of vereisen automatisering zijn. Controleer altijd celverwijzingen en formuleaanpassingen wanneer je je brongegevens wijzigt. Als je fouten tegenkomt, controleer dan bereiken of uitsluitingslijsten en probeer je formules opnieuw toe te passen of de macro opnieuw uit te voeren.
Gerelateerde artikelen:
- Hoe bepaalde cellen of gebied uitsluiten van afdrukken in Excel?
- Hoe waarden in één lijst uitsluiten van een andere in Excel?
- Hoe de minimumwaarde vinden in een bereik zonder de waarde nul in Excel?
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen