Unieke waarden tellen in Excel: dit moet je weten

In deze gids laat ik je zien hoe je unieke waarden in Excel telt. Ik laat ook speciale gevallen zien, zoals hoe je omgaat met hoofdlettergevoeligheid of hoe je unieke waarden telt op basis van één of meer voorwaarden.

Wil je je Excel-skills verder uitbouwen? Schrijf je in voor onze skill track Excel Fundamentals en word een expert.

Unieke waarden vs. distincte waarden in Excel

De begrippen worden soms door elkaar gehaald, maar unieke waarden en distincte waarden zijn niet hetzelfde.

• Distincte waarden zijn de verschillende waarden in een dataset, waarbij duplicaten zijn verwijderd. In de lijst A, A, B, C, C, D zijn de distincte waarden A, B, C, D. 
• Unieke waarden zijn daarentegen waarden die maar één keer in de dataset voorkomen. In hetzelfde voorbeeld zijn de unieke waarden B en D (omdat A en C vaker voorkomen).

In de eerste helft van het artikel focus ik op verschillende manieren om unieke waarden in Excel te tellen. Maar als je hier per ongeluk terecht bent gekomen en eigenlijk distincte waarden wilt tellen, dan heb ik daar later in het artikel ook methoden voor.

Twee manieren om unieke waarden in Excel te tellen

Er zijn twee gangbare manieren om unieke waarden in Excel te tellen.

Unieke waarden tellen in Excel met de functie UNIQUE()

De makkelijkste manier om unieke waarden te tellen is met de functie UNIQUE() en de functie COUNTA(). De functie UNIQUE() haalt alle unieke waarden uit een bereik, en COUNTA() telt hoeveel unieke waarden er zijn. 

COUNTA(UNIQUE(range, false, true))

Zo heb ik de volgende formule toegepast op een kleine dataset, en die gaf me de unieke waarden.

=COUNTA(UNIQUE(A2:A8, false, true))

Als je in plaats daarvan deze syntaxis gebruikt, =COUNTA(UNIQUE(A2:A8)), dus zonder de derde parameter die we op TRUE zetten, dan krijg je als resultaat een distincte telling, geen unieke. Door TRUE in het derde argument te zetten, geef je aan dat de functie een unieke telling moet teruggeven. Beide mogelijkheden zitten in de functie UNIQUE(), wat in het begin wat verwarrend kan zijn.

Unieke waarden tellen in Excel met SUM() en COUNTIF()

Als je niet helemaal zeker bent van de nuances van de functie UNIQUE(), of als je geen Excel 365 hebt, kun je ook SUM() combineren met IF() en COUNTIF() om de unieke waarden te tellen.

=SUM(IF(COUNTIF(range, range)=1,1,0))

Stel, ik heb gegevens in het bereik A2:A8 en ik wil de unieke waarden tellen, dan schrijf ik de volgende formule:

SUM(IF(COUNTIF(A2:A8, A2:A8)=1,1,0))

Hier loopt COUNTIF() de lijst door en controleert hoe vaak elke naam voorkomt. Als een naam maar één keer voorkomt, telt die als 1. Komt hij vaker voor, dan krijgt hij een hoger getal. Vervolgens filtert IF() die resultaten zodat de 1’tjes blijven staan en al het andere 0 wordt. Tot slot telt SUM() alle 1’tjes bij elkaar op en krijgen we het totale aantal unieke waarden.

Unieke gegevenstypen tellen in Excel

Soms bevat onze dataset gemengde gegevenstypen en moeten we die apart analyseren. Dat lijkt in het begin misschien wat ontmoedigend, maar het kan in Excel. Laten we twee methoden bekijken — één om unieke tekstwaarden te tellen en één voor unieke getallen.

Unieke tekstwaarden tellen in Excel

Wil je unieke tekstwaarden tellen, combineer dan de functies ISTEXT(), COUNTIF() en SUM(). ISTEXT() controleert of een waarde tekst is, COUNTIF() telt hoe vaak elke waarde voorkomt en SUM() telt de unieke tekstitems op. 

Ik heb de volgende formule op voorbeeldgegevens toegepast en die liet meteen zien hoeveel unieke tekstwaarden er zijn.

=SUM(IF(ISTEXT(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Unieke numerieke waarden tellen in Excel

Wil je unieke getallen tellen in plaats van tekst, gebruik dan dezelfde formule maar vervang ISTEXT() door ISNUMBER().

ISNUMBER() beschouwt alleen numerieke waarden, terwijl COUNTIF() en SUM() de uniciteit afhandelen. Ik heb de bovenstaande formule voor dezelfde gegevens aangepast en nu toont die alleen het aantal unieke numerieke waarden:

=SUM(IF(ISNUMBER(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))

Unieke waarden tellen met voorwaarden in Excel

Nu we de basismethoden om unieke waarden te tellen hebben behandeld, gaan we een paar geavanceerde technieken verkennen.

Unieke waarden tellen op basis van voorwaarden

Om de unieke waarden op basis van een specifieke voorwaarde te tellen, gebruik ik deze syntaxis:

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)

In deze formule filtert FILTER() het bereik op basis van specifieke criteria, verwijdert UNIQUE() duplicaten, telt ROWS() de resultaten en voorkomt IFERROR() fouten door Not Found te retourneren als er geen overeenkomsten zijn.

Stel, ik heb een lijst met Medewerkers en hun Afdelingen, en ik moet tellen hoeveel unieke medewerkers er in een specifieke afdeling werken. Dan gebruik ik de volgende formule:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")

Hier stelt A2:A10 het bereik met medewerkersnamen voor dat ik wil filteren en controleert B2:B10=F1 welke medewerkers tot de afdeling behoren die in F1 staat.

Unieke rijen tellen op basis van meerdere kolommen

Als ik unieke medewerkers in een specifieke afdeling wil tellen van wie het salaris minder is dan $50.000, gebruik ik deze formule:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")

Hier stelt A2:A10 het bereik met medewerkersnamen voor, controleert B2:B10=F1 welke medewerkers tot de afdeling behoren die in F1 staat, en controleert C2:C10>F2 of het salaris lager is dan het bedrag in F2.

Omgaan met hoofdlettergevoelige tellingen van unieke waarden

Standaard is Excel hoofdlettergevoelig. Zo worden Apple en APPLE als verschillend gezien. Om zulke gevallen te vinden maak ik een hulpkolom met de volgende formule:

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")

En om nu het aantal unieke waarden te krijgen, gebruik ik de functie COUNTIF() als volgt:

=COUNTIF(B2:B11,"Unique")

In plaats daarvan distincte waarden tellen

Als laatste onderdeel, voor het geval je eigenlijk distincte waarden wilde tellen, laat ik je verschillende methoden zien om distincte waarden te tellen. Maar eerst een tabel om de verschillen te laten zien:

Distincte waarden tellen in Excel met COUNTIF() en SUM()

Om distincte waarden in Excel te tellen, kun je de functies COUNTIF() en SUM() combineren. De functie COUNTIF() controleert hoe vaak elke waarde in een bepaald bereik voorkomt. Vervolgens telt SUM() de waarden op die door COUNTIF() zijn geretourneerd, wat het totale aantal distincte items oplevert. 

=SUM(1/COUNTIF(range, range))

Stel dat ik de distincte waarden in het bereik A2:A8 wil vinden. Dan voer ik de volgende formule in:

=SUM(1/COUNTIF(A2:A8, A2:A8))

Hier controleert de functie COUNTIF() hoe vaak elke waarde in een lijst voorkomt.

1/COUNTIF(A2:A8, A2:A8) deelt 1 door het aantal keren dat elke waarde voorkomt. Als een getal bijvoorbeeld twee keer voorkomt, wordt elk exemplaar 0,5 (1/2). Komt het drie keer voor, dan wordt het 0,33 (1/3). 

Omdat we deze breuken niet willen, kunnen we SUM() of SUMPRODUCT() gebruiken om alle waarden op te tellen. De breuken van duplicaten vormen samen 1, en distincte waarden blijven 1 omdat 1/1 = 1:

=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))

Distincte waarden tellen in Excel met draaitabellen

Excel 2013 en Excel 2016 hebben een ingebouwde functie om distincte waarden in een draaitabel te tellen. Om dit te gebruiken, maak je eerst een draaitabel door gegevens te selecteren: ga naar Invoegen en kies Draaitabel. Er verschijnt een dialoogvenster — kies daarin Bestaand werkblad en vink het vakje Deze gegevens toevoegen aan het datamodel aan.

Sleep vervolgens je gewenste kolom naar het veld Waarden. In mijn geval sleep ik de kolom Fruit omdat ik de distincte waarden daarvan wil tellen. Klik daarna op Instellingen waardeveld in het vervolgkeuzemenu. Er verschijnt een pop-up — selecteer daar Distincte telling. Je kunt deze kolom zelfs een aangepaste naam geven, maar dat is optioneel.

Distincte waarden tellen in Excel met filters

Werk je liever niet met formules, gebruik dan de geavanceerde filters. 

In dit voorbeeld heb ik een dataset en wil ik distincte waarden vinden met geavanceerde filters. Daarvoor selecteer ik het bereik waarin ik de betreffende waarde wil vinden. Vervolgens ga ik naar het tabblad Gegevens en klik ik op Geavanceerd. 

In het dialoogvenster Geavanceerd filter kies ik Kopiëren naar andere locatie, en in het veld Kopiëren naar vul ik de doelcel in waar ik mijn lijst wil laten verschijnen. Vink vervolgens het vakje Alleen unieke records aan en klik op OK.

Zodra ik een lijst met unieke waarden heb, bereken ik daarna het aantal van deze unieke waarden met de functie ROWS():

=ROWS(D2:D5)

Hoewel de optie in de onderstaande afbeelding Alleen unieke records aangeeft, worden er in feite distincte waarden geëxtraheerd. 

Distincte waarden tellen in Excel met VBA-macro’s

Als je regelmatig distincte waarden wilt tellen, gebruik dan VBA (Visual Basic for Applications). Je schrijft de code één keer en automatiseert het hele proces telkens wanneer je de functie bij naam aanroept.

Zo doe je dat: druk op Alt + F11 en ga naar de optie Module onder het tabblad Invoegen. Er verschijnt een nieuwe module, en daar kun je je code plakken. 

Dit is de code die ik heb gemaakt om de distincte waarden te tellen:

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End Function

Druk daarna op Ctrl + S om op te slaan en op ALt + Q om de VBA-editor te sluiten. Je kunt deze aangepaste functie nu overal in het blad aanroepen en het bereik opgeven waarin je de distincte waarden wilt vinden. In mijn geval heet hij CountUnique(), dus elke keer dat ik deze functie met een opgegeven bereik aanroep, geeft hij me een telwaarde:

=CountUnique(A2:A8)

Tot slot

Ik heb de belangrijkste formules, draaitabellen en VBA-macro’s behandeld voor het tellen van unieke en distincte waarden. Elke methode heeft zijn voordelen, maar kies wat past bij jouw behoeften en ondersteund wordt door jouw Excel-versie.

Wil je je Excel-vaardigheden aanscherpen? Bekijk dan onze skill track Excel Fundamentals en de cursus Data Preparation in Excel.