Hoe kan ik alleen zichtbare cellen optellen op basis van criteria in Excel?
In Excel kunnen gebruikers doorgaans cellen optellen op basis van specifieke criteria met behulp van de functie SOMMEN.ALS. Echter, bij het omgaan met gefilterde gegevens zal het eenvoudig toepassen van SOMMEN.ALS zowel zichtbare als verborgen cellen meenemen in de berekening. Dit leidt vaak tot onjuiste resultaten als je alleen de zichtbare (d.w.z. niet-gefilterde) cellen die aan bepaalde criteria voldoen wilt optellen, zoals te zien is in de onderstaande schermafbeelding.
Alleen zichtbare cellen optellen op basis van één of meerdere criteria met een hulpcolumn
Alleen zichtbare cellen optellen op basis van één of meerdere criteria met een formule
Alleen zichtbare cellen optellen op basis van criteria met VBA-code
Het is een veelvoorkomende behoefte in dagelijkse rapportage- en data-analyseprocessen om gegevens in gefilterde tabellen nauwkeurig samen te vatten, zoals bijvoorbeeld bij het berekenen van verkoopbedragen voor een bepaald product of categorie nadat enkele filters zijn toegepast. Het foutief uitvoeren hiervan kan resulteren in totalen die gegevens bevatten die je niet bedoelde, dus is het belangrijk om technieken te gebruiken die alleen de zichtbare gegevens optellen die je op je scherm ziet.
Dit artikel introduceert verschillende praktische methoden die geschikt zijn voor verschillende scenario's en vaardigheidsniveaus, elk met hun voordelen en mogelijke beperkingen. Je kunt een oplossing kiezen die het beste past bij de grootte van je werkblad, de structuur van je gegevens en je operationele gewoontes. Gedetailleerde stappen voor elke oplossing worden hieronder beschreven, 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 meerdere criteria met een hulpcolumn
Een van de meest intuïtieve en stabiele benaderingen om zichtbare cellen op te tellen op basis van specifieke criteria is het gebruik van een hulpcolumn die alleen waarden retourneert voor zichtbare rijen, en vervolgens de SOMMEN.ALS-functie gebruiken met je gewenste voorwaarden. Dit is vooral effectief als je dataset vaak op verschillende manieren wordt gefilterd of als je berekeningen moet opzetten die collega's gemakkelijk kunnen begrijpen of wijzigen.
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; mogelijk moeten formules worden bijgewerkt als de rijindeling verandert; uitgebreid gebruik kan omslachtig worden bij zeer grote datasets.
Bijvoorbeeld, om alleen de waarden van orders voor het product "Hoodie" in een gefilterd bereik op te tellen:
1. Voer of kopieer de volgende formule in een lege kolom naast je dataset in (bijvoorbeeld in cel E2, ervan uitgaande dat D je waardekolom is):
Sleep de vulgreep naar beneden om deze formule door alle rijen in je gegevensbereik te vullen. Deze formule retourneert de waarde uit kolom D als de rij zichtbaar is en 0 als de rij door filteren verborgen is.
2. Nadat je de hulpwaarden in kolom E hebt gegenereerd, gebruik je een SOMMEN.ALS-functie om alleen de zichtbare waarden op te tellen op basis van je criteria. Bijvoorbeeld, om op te tellen voor "Hoodie" in kolom A:
Je kunt meer criteria toevoegen door de SOMMEN.ALS-argumenten uit te breiden in de volgende indeling: =SOMMEN.ALS(som_bereik, criteria_bereik1, criteria1, [criteria_bereik2, criteria2], [criteria_bereik3, criteria3], ...). Controleer altijd je bereiken om ervoor te zorgen dat ze correct zijn uitgelijnd en de verwachte resultaten opleveren.
Let op: Als je rijen herschikt, invoegt of verwijdert na het instellen van je formules, controleer dan dubbel om ervoor te zorgen dat alle referenties nog steeds overeenkomen met je gegevensstructuur. Soms kunnen fouten ontstaan door niet-uitgelijnde bereiken of het vergeten om je criteriacellen bij te werken.
Alleen zichtbare cellen optellen op basis van criteria met een formule
Als je een op formules gebaseerde oplossing prefereert die geen hulpcolumns vereist, kun je een combinatie van SOMPRODUCT, SUBTOTAAL, VERSCHUIVING, RIJ en MIN functies gebruiken om zichtbare cellen op te tellen volgens specifieke criteria. Deze aanpak is het beste geschikt voor ervaren Excel-gebruikers die bekend zijn met arrayformules, en is vooral nuttig als je je blad netjes wilt houden zonder extra kolommen.
Voordelen: Geen extra werkbladkolommen nodig; flexibel en dynamisch; formules worden direct bijgewerkt wanneer je filtert of criteria wijzigt.
Beperkingen: Formules kunnen moeilijk te lezen of debuggen zijn, vooral voor mensen die niet bekend zijn met arrayfuncties; prestaties kunnen traag zijn bij zeer grote tabellen.
Kopieer of voer de volgende formule in een lege cel in (bijvoorbeeld om zichtbare cellen op te tellen voor "Hoodie" in A2:A12, met daadwerkelijke waarden in D2:D12, en de criteria in A17):
Druk na het invoeren van de formule op Enter om het gewenste resultaat te krijgen, zoals hieronder te zien is:
Let goed op: Deze aanpak is gevoelig voor de gespecificeerde bereiken - niet-passende of overlappende bereiken kunnen fouten of onverwachte resultaten veroorzaken. Test randgevallen, vooral wanneer filteren het aantal of de positie van zichtbare rijen verandert.
Alleen zichtbare cellen optellen op basis van criteria met VBA-code
Voor gevorderde gebruikers biedt het gebruik van VBA een flexibele manier om alleen zichtbare cellen per specifieke criteria op te tellen, vooral bij het omgaan met complexe scenario's of grote datasets waar standaardformules last hebben van prestatieknelpunten of waar criteria-aantallen multi-conditie logica omvatten die moeilijk in één formule uit te drukken is. VBA kan door elke zichtbare rij itereren, testen op je voorwaarden en de som efficiënt berekenen. Dit is vooral geschikt voor herhaalde rapportagetaken of bij het automatiseren van samenvattingsberekeningen.
Voordelen: Kan gemakkelijk grote datasets, meerdere of dynamische criteria en complexe logica verwerken; proces wordt snel uitgevoerd, zelfs met duizenden rijen; vermindert risico op fouten door handmatige formulewijzigingen.
Beperkingen: Vereist het inschakelen van macro's; sommige gebruikers kennen mogelijk VBA niet of hebben niet voldoende rechten; wijzigingen vereisen toegang tot de Macro-editor. Maak altijd een back-up voordat je VBA op belangrijke datasets uitvoert.
1. Om te beginnen, open de VBA-editor door te klikken op Ontwikkelaarstools > Visual Basic. 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 
"Uitvoeren" knop (of druk op F5) om de code uit te voeren. Een dialoogvenster vraagt je om zowel het criteriabereik (zoals je productnamen), het waardebereik om op te tellen en welke waarde je als filter wilt (bijv., "Hoodie"). De macro telt alleen die zichtbare rijen op waar je criteria wordt voldaan en toont het resultaat in een pop-upmelding.
Praktische tips: Gebruik deze VBA-code wanneer je vaak je sommen opnieuw moet berekenen na het wijzigen van je gegevens of filters. Je kunt de VBA-code verder uitbreiden om te werken met meerdere criteria door meer invoerprompts of logische voorwaarden toe te voegen.
Problemen oplossen: Zorg er altijd voor dat de bereiken die je selecteert voor criteria en waarden hetzelfde aantal rijen hebben en behoren tot dezelfde kolommen als je gefilterde gegevens. Als de code een fout rapporteert of niet de verwachte som retourneert, controleer dan je filterinstellingen en actieve selectie.
Samenvatting suggesties: Voor data-analyse waarbij herhaaldelijk alleen zichtbare berekeningen nodig zijn, kan het opslaan van deze macro in je Persoonlijke Macro-werkmap je dagelijkse rapportages versnellen. Als een dialoogvenster niet verschijnt, controleer dan je macro-instellingen en beveiligingsrechten.
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