Tel unieke waarden in Excel met de COUNTIF-functie

In deze zelfstudie leert u hoe u unieke waarden in Excel kunt tellen met behulp van formules (COUNTIF-en SUMPRODUCT-functies).

Hoe unieke waarden in Excel te tellen

laten we zeggen dat we een gegevensset hebben zoals hieronder getoond:

voor deze tutorial zal ik het bereik A2:A10 noemen als Namen. In de toekomst zullen we dit benoemde bereik in de formules gebruiken.

See Also: How to create Named Ranges in Excel.

in deze gegevensverzameling is er een herhaling in het NAMENBEREIK., Om het aantal unieke namen uit deze dataset te krijgen (A2:A10), kunnen we een combinatie van COUNTIF en SUMPRODUCT functies gebruiken zoals hieronder getoond:

=SUMPRODUCT(1/COUNTIF(NAMES,NAMES))

Hoe werkt deze formule?

laten we deze formule opsplitsen om een beter begrip te krijgen:

• COUNTIF(NAMES,NAMES)
  • dit deel van de formule geeft een array terug. In het bovenstaande voorbeeld zou het {2;2;3;1;3;1;2;3;2}. De getallen geven hier aan hoe vaak een waarde voorkomt in het opgegeven cellenbereik.,
    bijvoorbeeld, de naam is Bob, die twee keer voorkomt in de lijst, dus het zou het nummer 2 voor Bob teruggeven. Op dezelfde manier komt Steve drie keer voor en wordt 3 geretourneerd voor Steve.
• 1 / COUNTIF (NAMES,NAMES)
  • dit deel van de formule geeft een array terug– {0.5;0.5;0.333333333333333;1;0.333333333333333;1;0.5;0.333333333333333;0.5}
    omdat we 1 hebben gedeeld door de array, geeft het deze array terug.
    bijvoorbeeld, het eerste element van de array hierboven was 2. Als 1 gedeeld wordt door 2, komt het terug .5.,
• SOMPRODUCT(1 / COUNTIF (NAMES,NAMES))
  • SOMPRODUCT voegt eenvoudig al deze getallen toe. Merk op dat als Bob tweemaal voorkomt in de lijst, de bovenstaande array terugkeert .5 waar Bob naam verscheen in de lijst. Op dezelfde manier, omdat Steve drie keer in de lijst verschijnt, keert de array terug .3333333 wanneer Steve naam verschijnt. Wanneer we de nummers voor elke naam toevoegen, zou het altijd 1 retourneren. En als we alle getallen toevoegen, zou het de totale telling van unieke namen in de lijst retourneren.

deze formule werkt prima totdat u geen lege cellen in het bereik hebt., Maar als je lege cellen hebt, retourneert het een # DIV / 0! fout.

hoe lege cellen te behandelen?

laten we eerst begrijpen waarom het een fout retourneert wanneer er een lege cel in het bereik is. Stel dat we de gegevensset hebben zoals hieronder getoond (met cel A3 leeg):

en omdat we 1 delen door deze hele array, geeft het een #DIV/0! fout.,

om deze delingsfout in het geval van lege cellen af te handelen, gebruikt u de onderstaande formule:

=SUMPRODUCT((1/COUNTIF(NAMES,NAMES&””)))

een wijziging die we hebben aangebracht in deze formule is het criterium deel van de COUNTIF functie. We hebben namen&”” gebruikt in plaats van namen. Door dit te doen, zou de formule het aantal lege cellen retourneren (eerder gaf het 0 terug waar er een lege cel was).

Notes: Deze formule zou lege cellen tellen als een unieke waarde en deze retourneren in het resultaat.,

in het bovenstaande voorbeeld zou het resultaat 5 moeten zijn, maar het geeft 6 terug omdat de lege cel wordt geteld als een van de unieke waarden.

Hier is de formule die zorgt voor de lege cellen en telt niet in het uiteindelijke resultaat:

=SOMPRODUCT((NAMEN<>””)/AANTAL.ALS(NAMEN,NAMEN&””))

In deze formule, in plaats van 1 als teller, hebben we de NAMEN<>””., Dit geeft een array van TRUEs en FALSEs. Het retourneert FALSE wanneer er een lege cel is. Aangezien TRUE gelijk is aan 1 En FALSE gelijk is aan 0 in berekeningen, worden lege cellen niet geteld omdat de teller 0 is (FALSE).

nu we het basis skelet van de formule klaar hebben, kunnen we een stap verder gaan en verschillende gegevenstypen tellen.

Hoe unieke waarden in Excel te tellen die tekst zijn

we gebruiken hetzelfde concept dat hierboven is besproken om de formule te maken die alleen tekstwaarden telt die uniek zijn.,

Hier is de formule die unieke tekstwaarden in Excel telt:

=SUMPRODUCT((ISTEXT(NAMES)/COUNTIF(NAMES,NAMES&””)))

alles wat we hebben gedaan is de formule ISTEXT(NAMES) als teller gebruikt. Het retourneert TRUE wanneer de cel tekst bevat, en FALSE indien dit niet het geval is. het telt geen lege cellen, maar telt cellen met een lege tekenreeks (“”).,

unieke waarden tellen in Excel die numeriek zijn

Hier is de formule die unieke numerieke waarden telt in Excel

=SUMPRODUCT((ISNUMBER(NAMES))/COUNTIF(NAMES,NAMES&””))

hier gebruiken we ISNUMBER(NAMES) als teller. Het retourneert TRUE wanneer de cel numeriek gegevenstype bevat, en FALSE als dit niet het geval is. het telt geen lege cellen.

u kunt ook de volgende Excel-Tutorials leuk vinden:

• Hoe cellen te tellen die tekstreeksen bevatten.
• Hoe het aantal woorden in Excel te tellen.,
• Tel cellen op basis van achtergrondkleur in Excel.
• met behulp van meerdere Criteria in Excel COUNTIF en COUNTIFS functie.