Hoe tel je het aantal unieke waarden in een bereik op basis van meerdere criteria in Excel?
In veel praktijkscenario’s gaat het niet alleen om het tellen van waarden, maar ook om het bepalen van hoeveel unieke items aan bepaalde voorwaarden voldoen binnen uw gegevens. Zo wilt u bijvoorbeeld weten hoeveel verschillende producten een specifieke verkoper heeft verkocht, of hoeveel unieke bestellingen zijn geplaatst binnen een bepaalde periode. Om dergelijke taken efficiënt uit te voeren in Excel, is het essentieel om vertrouwd te raken met geschikte formules, geavanceerde functies zoals draaitabellen, of zelfs aangepaste VBA-oplossingen. In dit artikel bespreken we diverse praktische methoden om het aantal unieke waarden in een bereik te tellen op basis van één of meer criteria, inclusief stapsgewijze instructies en handige tips.
Tel het aantal unieke waarden in een bereik op basis van één criterium
Tel het aantal unieke waarden in een bereik op basis van twee opgegeven datums
Tel het aantal unieke waarden in een bereik op basis van twee criteria
Tel het aantal unieke waarden in een bereik op basis van drie criteria
Tel het aantal unieke waarden in een bereik met Draaitabel (Uniek aantal, Excel 2013+)
Tel het aantal unieke waarden in een bereik met VBA-code (voor complexe/geautomatiseerde gevallen)
Tel het aantal unieke waarden in een bereik op basis van één criterium
Laten we een veelvoorkomend scenario bekijken: u wilt weten hoeveel verschillende producten Tom heeft verkocht. Deze methode is ideaal voor eenvoudige datasets waarbij u uniciteit beoordeelt op basis van één voorwaarde, zoals de verkoopgegevens van één persoon. De aanpak is eenvoudig, maar vraagt wel zorgvuldig gebruik van matrixformules.
Voer voor dit scenario de volgende formule in een lege cel in (bijvoorbeeld cel G2):
=SUM(IF(„Tom"=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, „Tom", $A$2:$A$20, $A$2:$A$20)),0))
Druk na het typen van de formule op Ctrl + Shift + Enter (niet gewoon op Enter) om deze als matrixformule te bevestigen. Er verschijnen accolades rond de formule in de formulebalk, en u ziet het resultaat direct, zoals hieronder wordt weergegeven:
Opmerking:
- „Tom” is de voorwaarde die u wilt gebruiken om de resultaten te filteren. Vervang „Tom” door een verwijzing naar een andere cel (bijvoorbeeld $F$2) voor meer flexibiliteit.
- $C$2:$C$20 bevat de namen van de verkopers die geëvalueerd moeten worden.
- $A$2:$A$20 is de kolom met producten waarvoor u het aantal unieke items wilt bepalen.
- Als uw gegevensbereik wijzigt, vergeet dan niet de verwijzingen daarop aan te passen.
Tip: Als u Excel 365 of Excel 2019 (of nieuwer) gebruikt, kunt u de functies UNIQUE en FILTER proberen voor eenvoudigere formules.
Als u #VERD/0!-fouten tegenkomt, controleert u de criteria nogmaals en zorgt u ervoor dat uw bereiken even lang zijn.
Tel het aantal unieke waarden in een bereik op basis van twee opgegeven datums
Wanneer u het aantal unieke items binnen een specifieke datumreeks wilt bepalen—bijvoorbeeld alle unieke producten verkocht tussen 1-9-2016 en 30-9-2016—dan is deze aanpak ideaal. Ze is vooral handig bij het analyseren van datatrends over bepaalde perioden, zoals maandelijks, per kwartaal of binnen een aangepaste datumreeks. Let er wel op dat de datumopmaak exact overeenkomt met de datumwaarden in uw werkblad.
Plaats de volgende formule in een lege cel waar u het resultaat wilt weergeven:
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, „="&DATE(2016,9,1))),0)
Druk op Ctrl + Shift + Enter nadat u de formule hebt ingevoerd om deze uit te voeren als een matrixformule. De onderstaande afbeelding toont het resultaat:
Opmerking:
- 2016,9,1 en 2016,9,30 zijn de begin- en einddatumcriteria. U kunt deze naar wens aanpassen of zelfs celverwijzingen gebruiken voor dynamische datumfilters.
- $D$2:$D$20 bevat de datumwaarden die moeten worden gecontroleerd.
- $A$2:$A$20 is opnieuw de kolom met items of producten die u uniek wilt tellen.
- Zorg ervoor dat uw datums als geldige Excel-datums zijn opgeslagen, niet als tekst. Als het resultaat niet naar verwachting wordt weergegeven, controleer dan uw datumnotatie en bereiken.
Tip: Gebruik DATUM(jaar; maand; dag) om problemen met regionale datumnotatie te voorkomen. Overweeg bij het werken met dynamische bereiken het gebruik van benoemde bereiken voor meer duidelijkheid.
Tel het aantal unieke waarden in een bereik op basis van twee criteria
Stel dat u alleen de producten wilt analyseren die Tom in september heeft verkocht, waarbij u naam en een Datumreeks combineert in uw unieke telling. Dit scenario komt veel voor bij periodieke prestatiebeoordelingen of gesegmenteerde analyses. Naarmate uw criteria uitbreiden, wordt de formule complexer en wordt aandacht voor gegevensnauwkeurigheid nog belangrijker.
Voer de onderstaande formule in een willekeurige lege cel in, zoals H2:
=SUM(IF((„Tom"=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, „Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, „="&DATE(2016,9,1))),0)
Bevestig de formule na het typen met Ctrl + Shift + Enter. U ziet direct het unieke aantal; zie de volgende afbeelding:
Opmerkingen:
- „Tom” is het naamcriterium, terwijl „2016,9,1” en „2016,9,30” de grenzen van uw datumreeks vormen. Pas deze naar wens aan of maak ze dynamisch met behulp van celverwijzingen.
- $C$2:$C$20 is de personeelskolom (of een andere eerste criteriumkolom); $D$2:$D$20 is de datumkolom; en $A$2:$A$20 bevat de unieke items die geteld moeten worden.
- Alle bereiken moeten even lang zijn om fouten te voorkomen.
Als u „of”-voorwaarden wilt gebruiken, zoals het tellen van unieke producten verkocht door Tom of in de regio Zuid, kunt u de volgende formule gebruiken. Hiermee kunt u bredere zoekvoorwaarden instellen, hoewel resultaten kunnen overlappen als gegevens aan beide criteria voldoen:
=SOM(--(FREQUENTIE(ALS((„Tom"=$C$2:$C$20)+(„South"=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
Vergeet niet op Ctrl + Shift + Enter te drukken. U ziet de resultaten zoals hieronder wordt weergegeven:
Tip: Let op dubbele tellingen bij het toepassen van OF-criteria—als één en hetzelfde record aan beide voorwaarden voldoet, kan dat de prestaties beïnvloeden, vooral bij grote datasets.
Tel het aantal unieke waarden in een bereik op basis van drie criteria
Soms vraagt uw analyse om drie of meer voorwaarden, zoals het identificeren van unieke producten die uitsluitend in september door Tom zijn verkocht in de regio Noord. Dit komt veelvuldig voor bij multidimensionale data-analyse voor rapportage of gerichte bedrijfsinzichten. Een zorgvuldig beheer van verwijzingen is essentieel bij het toepassen van dit soort samengestelde logica.
Plaats deze matrixformule in een lege cel (bijvoorbeeld I2):
=SUM(IF((„Tom"=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(„North"=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, „Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, „="&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Druk op Ctrl + Shift + Enter om te voltooien. Hieronder ziet u een voorbeeldresultaat ter referentie:
Controleer bij geavanceerde voorwaarden altijd of alle bereiken consistent zijn en of de gegevenstypen (zoals datum en tekst) correct zijn — inconsistenties kunnen fouten of misleidende resultaten veroorzaken.
Tips:
- Als u prestatieproblemen ondervindt bij grote datasets, overweeg dan de formule op te splitsen of de Draaitabel-oplossing van Excel te gebruiken.
- Het gebruik van benoemde bereiken of celverwijzingen voor alle criteria verbetert de leesbaarheid en vermindert formulefouten.
- Overweeg voor veelvuldig gebruik om deze formules op te nemen in benoemde celverwijzingen of aangepaste functies.
Tel het aantal unieke waarden in een bereik met Draaitabel (Unieke telling, Excel 2013+)
Voor gebruikers van Excel 2013 of nieuwer biedt een draaitabel een interactiever alternatief zonder formules om het aantal unieke waarden in een bereik te tellen op basis van één of meerdere criteria. Met de functie Unieke telling kunt u grote datasets efficiënt samenvatten en filteren – ideaal voor dynamische, rapportagegerichte omgevingen. Houd er rekening mee dat oudere Excel-versies de functie Unieke telling in draaitabellen niet ondersteunen.
Zo gebruikt u deze methode:
- Selecteer uw dataset en ga naar Invoegen > PivotTable.
- Selecteer in het dialoogvenster PivotTable maken waar u de PivotTable wilt plaatsen, vink het vakje „Deze gegevens toevoegen aan het gegevensmodel” aan en klik daarna op OK.
- Sleep het veld dat u uniek wilt tellen (bijvoorbeeld Product) naar het waardengebied. Standaard wordt dit weergegeven als ‘Aantal van...’.
- Klik op het veld in het waardengebied en selecteer Waarde Veldinstellingen.
- Selecteer in het pop-updialoogvenster onderaan Uniek aantal (deze optie is alleen beschikbaar in Excel 2013 of nieuwer en verschijnt wanneer de draaitabel is gemaakt met de optie ‘Deze gegevens toevoegen aan het gegevensmodel’ ingeschakeld).
- Voeg uw criteriavelden (bijvoorbeeld Verkoper, Regio of Datum) toe aan het filter- of rijen/kolommengebied om één of meerdere voorwaarden toe te passen.
- Uw draaitabel toont nu het unieke aantal waarden, gefilterd op uw geselecteerde criteria.
Voordelen: Zeer visueel, eenvoudig aanpasbare filters zonder dat u formules hoeft te bewerken, en perfect geschikt voor interactieve rapportage.
Beperkingen: Niet beschikbaar in Excel 2010 of ouder; bij het toevoegen van nieuwe gegevens moet u de draaitabel handmatig vernieuwen.
Praktische tip: Zorg er altijd voor dat de brongegevens geen duplicaten bevatten binnen hetzelfde record, tenzij dat de bedoeling is. Als u de optie Unieke telling niet ziet, maakt u de draaitabel opnieuw aan en schakelt u de optie „Deze gegevens toevoegen aan het gegevensmodel” in.
Tel het aantal unieke waarden in een bereik met VBA-code (voor complexe/geautomatiseerde gevallen)
Soms moet u automatisch het aantal unieke waarden in een bereik tellen op basis van verschillende criteria – vooral bij zeer grote datasets of herhaalde analyses. Een VBA-macro is in dergelijke situaties ideaal, omdat deze na de initiële instelling diverse logica, inclusief filteren op meerdere voorwaarden, razendsnel verwerkt zonder handmatige tussenkomst. VBA is echter geavanceerder dan standaard Excel-functies en daarom het meest geschikt voor gebruikers die al bekend zijn met macro’s of continue analytische behoeften hebben.
Werkstappen:
- Druk op Alt + F11 om de VBA-editor te openen. Selecteer in de editor Invoegen > Module om een nieuwe module te maken.
- Kopieer en plak de volgende VBA-code in de module:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Sluit de VBA-editor en keer terug naar uw werkblad. Druk op Alt + F8, selecteer CountUniqueWithCriteria en voer de macro uit.
- Volg de invoerprompten om uw gegevensgebieden en criteria op te geven. Het resultaat verschijnt zowel in de cel van uw keuze als in een berichtvenster.
Uitleg en opmerkingen bij parameters:
- Deze macro is momenteel ingesteld voor één criterium. Wilt u deze uitbreiden naar meerdere criteria? Pas dan de
If ... Then-logica binnen de lus aan. - Sla uw werkmap altijd op voordat u macro’s uitvoert, want wijzigingen kunnen niet ongedaan worden gemaakt.
- Schakel macro’s in uw Excel-instellingen in als u uitvoeringsfouten ondervindt.
- Deze methode is ideaal voor grotere of regelmatig bijgewerkte datasets waar handmatige formules te omslachtig zouden zijn.
Voordelen: Zeer aanpasbaar en uitstekend te automatiseren, efficiënt geschikt voor grote en dynamische datasets. Ideaal voor geavanceerde of herhaalde workflows.
Nadelen: Vereist macro-machtigingen, en beginners hebben mogelijk wat tijd nodig om vertrouwd te raken met VBA-bewerkingen.
Controleer bij het werken met tellingen van unieke waarden op basis van criteria altijd uw bereikverwijzingen en zorg ervoor dat alle criteriakolommen qua grootte exact overeenkomen. Niet-overeenkomende bereiken zijn een veelvoorkomende oorzaak van fouten of onjuiste resultaten. Als formules onverwachte uitkomsten geven, controleer dan op verborgen opmaakproblemen of lege cellen. Voor prestatiegevoelige scenario’s bieden draaitabellen en VBA robuuste alternatieven voor matrixformules. Kies de oplossing die het beste aansluit bij uw kennisniveau en de complexiteit van uw dataset. Onthoud dat Kutools voor Excel extra hulpmiddelen en sneltoetsen biedt die veel van deze taken stroomlijnen voor nog grotere efficiëntie in complexe werkmappen.
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