Generieke formule
=SUMPRODUCT(1/COUNTIF(data,data))
Samenvatting
Om het aantal unieke waarden in een celbereik te tellen, kunt u een formule gebruiken die is gebaseerd op de functies AANTAL.ALS en SOMPRODUCT. In de voorbeeldshow is de formule in F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Uitleg
AANTAL.ALS werkt van binnen naar buiten en is geconfigureerd op waarden in het bereik B5: B14, waarbij al deze dezelfde waarden als criteria worden gebruikt:
COUNTIF(B5:B14,B5:B14)
Omdat we 10 waarden voor criteria opgeven, krijgen we een array met 10 resultaten als volgt terug:
(3;3;3;2;2;3;3;3;2;2)
Elk getal staat voor een telling - "Jim" verschijnt 3 keer, "Sue" verschijnt 2 keer, enzovoort.
Deze array is geconfigureerd als een deler met 1 als teller. Na deling krijgen we nog een array:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Alle waarden die slechts één keer in het bereik voorkomen, worden weergegeven als 1s, maar waarden die meerdere keren voorkomen, worden weergegeven als fractionele waarden die overeenkomen met het veelvoud. (dwz een waarde die 4 keer in de gegevens voorkomt, genereert 4 waarden = 0,25).
Ten slotte telt de functie SOMPRODUCT alle waarden in de array op en retourneert het resultaat.
Omgaan met lege cellen
Een manier om met lege of lege cellen om te gaan, is door de formule als volgt aan te passen:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Door een lege string ("") aan de gegevens samen te voegen, voorkomen we dat nullen in de array terechtkomen die door AANTAL.ALS is gemaakt als er lege cellen in de gegevens staan. Dit is belangrijk, omdat een nul in de deler ervoor zorgt dat de formule een # DIV / 0-fout genereert. Het werkt omdat het gebruik van een lege string ("") voor criteria lege cellen telt.
Hoewel deze versie van de formule geen # DIV / 0-fout genereert wanneer er lege cellen zijn, worden er lege cellen in de telling opgenomen. Als u lege cellen van de telling wilt uitsluiten, gebruikt u:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Dit heeft tot gevolg dat het aantal lege cellen wordt opgeheven door de teller nul te maken voor de bijbehorende tellingen.
Trage prestaties?
Dit is een coole en elegante formule, maar hij berekent veel langzamer dan formules die FREQUENCY gebruiken om unieke waarden te tellen. Voor grotere gegevenssets wilt u mogelijk overschakelen naar een formule op basis van de functie FREQUENCY. Hier is een formule voor numerieke waarden en een voor tekstwaarden.
