Unieke waarden tellen in Excel

In Excel 365 en Excel 2021 is uniek tellen een korte formule:

=AANTALARG(UNIEK(B2:B100))

De formule werkt van binnen naar buiten. UNIEK maakt van het bereik een lijst waarin elke waarde één keer voorkomt. Die lijst zie je niet, want hij blijft binnen de formule. AANTALARG telt daarna hoeveel waarden er in die lijst staan.

Staan er in een orderlijst acht regels van vijf verschillende klanten, dan geeft de formule 5.

AANTALARG telt tekst en getallen, dus de formule werkt ook voor klantnummers of postcodes. Je kunt ook =RIJEN(UNIEK(B2:B100)) gebruiken. RIJEN telt het aantal rijen van de lijst en geeft dezelfde uitkomst.

UNIEK maakt geen verschil tussen hoofdletters en kleine letters. Jansen en JANSEN tellen als één klant.

Met uniek kun je twee dingen bedoelen, en Excel kan ze allebei tellen:

• Verschillende waarden: elke waarde telt één keer mee, hoe vaak hij ook voorkomt. Dat telt =AANTALARG(UNIEK(B2:B100)).
• Waarden die precies één keer voorkomen: waarden die vaker in de lijst staan, tellen helemaal niet mee.

Voor de tweede vraag zet je WAAR als derde argument van UNIEK:

=AANTALARG(UNIEK(B2:B100;ONWAAR;WAAR))

Het tweede argument ONWAAR betekent dat UNIEK rijen met elkaar vergelijkt, zoals normaal. Je mag het ook leeg laten met twee puntkomma's achter elkaar.

Dit is handig voor vragen als: hoeveel klanten hebben maar één keer besteld? Of: welke artikelnummers komen maar één keer voor in de voorraadlijst?

Wil je alleen binnen een groep tellen, bijvoorbeeld het aantal verschillende klanten in regio Noord, zet dan FILTER binnen UNIEK:

=AANTALARG(UNIEK(FILTER(B2:B100;C2:C100="Noord")))

FILTER haalt eerst alle klanten uit Noord op. UNIEK haalt daar de dubbelen uit en AANTALARG telt wat er overblijft. Zet de regio liever in een cel, zoals F1, en schrijf C2:C100=F1. Dan wissel je van regio door alleen F1 aan te passen.

Meerdere voorwaarden vermenigvuldig je met een sterretje: FILTER(B2:B100;(C2:C100="Noord")*(D2:D100>500)).

Let op: vindt FILTER niets, dan geeft het een foutmelding en AANTALARG telt die als 1. Vang dat op met ALS:

=ALS(AANTAL.ALS(C2:C100;F1)=0;0;AANTALARG(UNIEK(FILTER(B2:B100;C2:C100=F1))))

UNIEK en FILTER bestaan niet in Excel 2019 en ouder. Daar krijg je #NAAM?. Gebruik dan deze formule:

=SOMPRODUCT(1/AANTAL.ALS(B2:B100;B2:B100))

Zo werkt het. AANTAL.ALS telt voor elke cel hoe vaak die waarde in het bereik voorkomt. Staat Jansen er drie keer in, dan krijgt elke Jansen een 3. Met 1/ wordt dat drie keer een derde, en samen is dat precies 1. Zo telt elke waarde één keer mee, en SOMPRODUCT telt alles op.

Staan er lege cellen in het bereik, dan krijg je #DEEL/0!. Gebruik dan:

=SOMPRODUCT((B2:B100<>"")/AANTAL.ALS(B2:B100;B2:B100&""))

Bij lijsten met duizenden rijen wordt deze formule traag. Zet de kolom dan in een draaitabel bij Rijen. Het aantal rijen in de draaitabel is het aantal unieke waarden.

Twee dingen maken de telling vaak te hoog.

Lege cellen. Staat er een lege cel in het bereik, dan neemt UNIEK die mee als extra waarde. In de lijst zie je dan een 0, en AANTALARG telt er één te veel. Filter de lege cellen eruit:

=AANTALARG(UNIEK(FILTER(B2:B100;B2:B100<>"")))

Dat is ook handig als je het bereik ruim neemt, zodat nieuwe regels vanzelf meetellen.

Verschillen die je niet ziet. Bakkerij Vos met een spatie erachter is voor Excel een andere waarde dan Bakkerij Vos zonder spatie. Hetzelfde geldt voor Garage Smit en Garage Smit BV. Maak de kolom eerst schoon met SPATIES.WISSEN en spreek één vaste schrijfwijze af. Hoofdletters maken niet uit.