Generieke formule
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Samenvatting
Om unieke waarden met een of meer voorwaarden te tellen, kunt u een formule gebruiken die is gebaseerd op UNIQUE en FILTER. In het getoonde voorbeeld is de formule in H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
wat 3 retourneert, aangezien er drie unieke namen zijn in B6: B15 die zijn gekoppeld aan het Omega-project.
Opmerking: voor deze formule zijn Dynamic Array Formulas vereist, die alleen beschikbaar zijn in Excel 365. Met een oudere versie van Excel kunt u complexere alternatieve formules gebruiken.
Uitleg
In de kern gebruikt deze formule de functie UNIEK om unieke waarden te extraheren, en de functie FILTER past criteria toe.
Door van binnen naar buiten te werken, wordt de FILTER-functie gebruikt om criteria toe te passen en alleen namen te extraheren die zijn gekoppeld aan het "Omega" -project:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Het resultaat van FILTER is een array als deze:
("Jim";"Jim";"Carl";"Sue";"Carl")
Vervolgens wordt de functie UNIQUE gebruikt om duplicaten te verwijderen:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
wat resulteert in een nieuwe array zoals deze:
("Jim";"Carl";"Sue") // after UNIQUE
Op dit punt hebben we een unieke lijst met namen die aan Omega zijn gekoppeld, en we hoeven ze alleen maar te tellen. Om onderstaande redenen doen we dit met de LEN-functie en de SOM-functie. Om het duidelijk te maken, zullen we eerst de formule herschrijven om de unieke lijst op te nemen:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
De functie LEN haalt de lengte van elk item in de lijst op en retourneert een reeks lengtes:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Vervolgens controleren we of lengtes groter zijn dan nul:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
En gebruik een dubbel negatief om de TRUE en FALSE waarden naar 1s en 0s te dwingen:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Ten slotte tellen we de resultaten op met de SOM-functie:
=SUM((1;1;1)) // returns 3
Deze array wordt rechtstreeks aan de COUNTA-functie geleverd, die een laatste telling retourneert:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Merk op dat omdat we de lengte controleren van elk item dat wordt geretourneerd door UNIEK, lege of lege cellen die aan de criteria voldoen, worden genegeerd. Deze formule is dynamisch en wordt onmiddellijk opnieuw berekend als de brongegevens worden gewijzigd.
Reken uniek met meerdere criteria
Als u unieke waarden wilt tellen op basis van meerdere criteria, kunt u de "include" -logica binnen FILTER uitbreiden. Gebruik bijvoorbeeld om unieke namen voor het Omega-project alleen in juni te tellen:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Dit is een voorbeeld van het gebruik van booleaanse logica om meer dan één voorwaarde toe te passen. De aanpak wordt hier in meer detail uitgelegd.
Zie deze trainingsvideo voor meer informatie: Filteren met meerdere criteria.
COUNTA
Het is mogelijk om een eenvoudigere formule te schrijven die antwoordt op de COUNTA-functie. Een belangrijk voorbehoud is echter dat COUNTA 1 retourneert als er geen overeenkomende waarden zijn. Dit komt doordat de functie FILTER een fout retourneert wanneer er geen gegevens overeenkomen met criteria, en deze fout wordt uiteindelijk geteld door de functie COUNTA. De basisformule van COUNTA ziet er als volgt uit:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Nogmaals, deze formule retourneert 1 als er geen overeenkomende gegevens zijn. Het bevat ook lege cellen die aan de criteria voldoen. De formule op basis van LEN en SUM is een betere optie.
Geen dynamische arrays
Als u een oudere versie van Excel gebruikt zonder ondersteuning voor dynamische matrixen, kunt u een complexere formule gebruiken. Zie Alternatieven voor dynamische matrixformules voor een meer algemene bespreking van alternatieven voor dynamische matrixen.
