Hoe telt u in Excel alleen de zichtbare cellen die voldoen aan bepaalde criteria?
In Excel gebruiken gebruikers doorgaans de functie SUMMEN.ALS om cellen op te tellen op basis van specifieke criteria. Wanneer u echter werkt met gefilterde gegevens, telt SUMMEN.ALS zowel zichtbare als verborgen cellen mee — wat vaak leidt tot onjuiste resultaten als u alleen de zichtbare (niet-gefilterde) cellen wilt optellen die aan bepaalde criteria voldoen, zoals in de onderstaande afbeelding wordt getoond.
Alleen zichtbare cellen optellen op basis van één of meer criteria met een hulpkolom
Alleen zichtbare cellen optellen op basis van één of meer criteria met een formule
Alleen zichtbare cellen optellen op basis van criteria met VBA-code
Het is een veelvoorkomende behoefte in dagelijkse rapportages en Data-analyse-workflows om gegevens in gefilterde tabellen nauwkeurig samen te voegen, bijvoorbeeld bij het berekenen van verkoopbedragen voor een bepaald product of categorie na het toepassen van filters. Onjuist uitvoeren kan resulteren in totalen die gegevens bevatten die u niet bedoelde, dus het is belangrijk om technieken te gebruiken die alleen de zichtbare gegevens op uw scherm optellen.
Dit artikel introduceert verschillende praktische methoden die geschikt zijn voor diverse scenario’s en niveaus van deskundigheid, elk met eigen voordelen en mogelijke beperkingen. U kunt een oplossing kiezen die het beste past bij de grootte van uw werkblad, de structuur van uw gegevens en uw werkgewoonten. Hieronder vindt u gedetailleerde stappen voor elke oplossing, samen met uitleg over mogelijke fouten en manieren om het berekeningsproces te optimaliseren voor betrouwbaardere resultaten.
Alleen zichtbare cellen optellen op basis van één of meer criteria met een hulpkolom
Een van de meest intuïtieve en betrouwbare methoden om zichtbare cellen op basis van specifieke criteria op te tellen, is het gebruik van een hulpkolom die alleen waarden retourneert voor zichtbare rijen, gecombineerd met de functie SUMMEN.ALS en uw gewenste voorwaarden. Deze aanpak werkt vooral goed wanneer uw dataset regelmatig op verschillende manieren wordt gefilterd of wanneer u berekeningen moet instellen die collega’s eenvoudig kunnen begrijpen en aanpassen.
Voordelen: Eenvoudig in te stellen; alle logica en berekeningen blijven zichtbaar in het werkblad; ideaal voor kleine tot middelgrote tabellen; robuust bij het aanpassen of controleren van formules.
Beperkingen: Creëert extra kolommen; formules moeten mogelijk worden bijgewerkt bij wijzigingen in de rijindeling; bij zeer grote datasets kan uitgebreid gebruik onhandig worden.
Bijvoorbeeld: om alleen de waarden van bestellingen voor het product „Hoodie” op te tellen in een Filterbereik:
1. Voer de volgende formule in of kopieer deze naar een lege kolom naast uw dataset (bijvoorbeeld in cel E2, aangenomen dat kolom D uw waardekolom is):
Sleep de vulgreep omlaag om deze formule naar alle rijen in uw gegevensbereik te kopiëren. De formule geeft de waarde uit kolom D terug als de rij zichtbaar is, en 0 als de rij verborgen is door filteren.
2. Nadat u de hulpwaarden in kolom E hebt gegenereerd, gebruikt u de functie SUMMEN.ALS om alleen de zichtbare waarden op te tellen die voldoen aan uw criteria. Bijvoorbeeld, om waarden op te tellen voor „Hoodie” in kolom A:
U kunt meer criteria toevoegen door de argumenten van SUMMEN.ALS uit te breiden in het formaat =SUMMEN.ALS(som_bereik; criterium_bereik1; criterium1; [criterium_bereik2; criterium2]; [criterium_bereik3; criterium3]; …). Controleer altijd uw bereiken om een correcte uitlijning en de verwachte resultaten te garanderen.
Let op: Als u rijen herschikt, invoegt of verwijdert nadat u uw formules hebt ingesteld, controleer dan zorgvuldig of alle verwijzingen nog steeds overeenkomen met uw gegevensstructuur. Fouten ontstaan soms doordat bereiken niet meer correct zijn uitgelijnd of doordat u uw criteriacellen bent vergeten bij te werken.
Alleen zichtbare cellen optellen op basis van criteria met een formule
Als u liever een oplossing op basis van formules gebruikt zonder hulpkolommen toe te voegen, combineert u de functies SOMPRODUCT, SUBTOTAAL, VERSCHUIVING, RIJ en MIN om zichtbare cellen op te tellen aan de hand van specifieke criteria. Deze aanpak is ideaal voor ervaren Excel-gebruikers die bekend zijn met matrixformules en perfect als u uw werkblad overzichtelijk wilt houden zonder extra kolommen.
Voordelen: Geen extra kolommen nodig; flexibel en dynamisch; de formule wordt direct bijgewerkt bij filteren of aanpassen van criteria.
Beperkingen: Formules kunnen lastig leesbaar of moeilijk te debuggen zijn, vooral voor gebruikers die niet vertrouwd zijn met matrixfuncties; bij zeer grote tabellen kan de prestatie bovendien trager worden.
Kopieer of voer de volgende formule in een lege cel in (bijvoorbeeld om zichtbare cellen op te tellen voor „Hoodie” in A2:A12, met Werkelijke waarde in D2:D12 en het criterium in A17):
Nadat u de formule hebt ingevoerd, drukt u op Enterom het gewenste resultaat te verkrijgen, zoals hieronder wordt getoond:
Let op: Deze aanpak is gevoelig voor de opgegeven bereiken – niet-overeenkomende of overlappende bereiken kunnen fouten of onverwachte resultaten opleveren. Test randgevallen, vooral wanneer filtering het aantal of de positie van zichtbare rijen wijzigt.
Alleen zichtbare cellen optellen op basis van criteria met VBA-code
Voor gevorderde gebruikers biedt VBA een flexibele oplossing om alleen zichtbare cellen op te tellen op basis van specifieke criteria – vooral in complexe scenario’s of bij grote datasets waar standaardformules lijden onder prestatieknelpunten, of wanneer het toepassen van meerdere voorwaarden logica vereist die moeilijk in één formule is uit te drukken. Met VBA kunt u elke zichtbare rij doorlopen, uw voorwaarden toetsen en de som efficiënt berekenen. Dit is dan ook bijzonder geschikt voor herhaalde rapportagetaken of het automatiseren van samenvattende berekeningen.
Voordelen: Verwerkt eenvoudig grote datasets, meerdere of dynamische criteria en complexe logica; voert het proces razendsnel uit, zelfs bij duizenden rijen; en vermindert het risico op fouten door handmatige formulewijzigingen.
Beperkingen: Vereist het inschakelen van macro’s. Sommige gebruikers zijn niet bekend met VBA of beschikken niet over voldoende rechten, en wijzigingen vereisen toegang tot de Macro-editor. Maak altijd een back-up voordat u VBA uitvoert op belangrijke datasets.
1. Open de VBA-editor door op Ontwikkelaarshulpmiddelen > Visual Basic te klikken. Ga in het venster dat verschijnt naar Invoegen > Module en plak de volgende code in de nieuwe module:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. Klik op de knop 
„Uitvoeren” (of druk op)F5) om de code uit te voeren. Vervolgens verschijnt een dialoogvenster waarin u het criteriabereik (bijvoorbeeld uw productnamen), het bijbehorende waardebereik dat moet worden opgeteld en de filterwaarde (bijv. „Hoodie”) kunt selecteren. De macro telt alleen de zichtbare rijen op die voldoen aan uw criterium en toont het resultaat in een pop-upbericht.
Praktische tips: Gebruik deze VBA-code wanneer u uw totalen vaak opnieuw moet berekenen na het wijzigen van gegevens of filters. U kunt de code verder uitbreiden om met meerdere criteria te werken door extra invoervensters of logische voorwaarden toe te voegen.
Probleemoplossing: Zorg er altijd voor dat de bereiken die u selecteert voor criteria en waarden evenveel rijen bevatten en tot dezelfde kolommen behoren als uw gefilterde gegevens. Als de code een foutmelding geeft of niet het verwachte totaal retourneert, controleer dan uw filterinstellingen en actieve selectie.
Samenvattende suggesties: Voor data-analyse waarbij u herhaaldelijk alleen zichtbare waarden moet berekenen, kunt u deze macro opslaan in uw persoonlijke macro-werkmap om uw dagelijkse rapportage flink te versnellen. Als er geen dialoogvenster verschijnt, controleer dan uw macro-instellingen en beveiligingsrechten.
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