Generieke formule
(=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)^0))>=N)))
Samenvatting
Als u rijen wilt tellen die specifieke waarden bevatten, kunt u een matrixformule gebruiken op basis van de functies MMULT, TRANSPOSE, COLUMN en SUM. In het getoonde voorbeeld is de formule in K6:
(=SUM(--(MMULT(--((data)=2)))
waarbij data het benoemde bereik C5: I14 is.
Merk op dat dit een matrixformule is en moet worden ingevoerd met control shift enter.
Uitleg
Als je van binnen naar buiten werkt, zijn de logische criteria die in deze formule worden gebruikt:
(data)<70
waarbij data het benoemde bereik C5: I14 is. Dit genereert een TRUE / FALSE-resultaat voor elke waarde in gegevens, en het dubbele negatieve resultaat dwingt de TRUE FALSE-waarden naar 1 en 0 om een array als deze te geven:
(0,0,0,1,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,1,0,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0)
Net als de originele gegevens is deze array 10 rijen bij 7 kolommen (10 x 7) en gaat deze in de MMULT-functie als array1 . Het volgende argument, array2, wordt gemaakt met:
TRANSPOSE(COLUMN(data)^0))
Hier wordt de COLUMN-functie gebruikt als een manier om een numerieke array van de juiste grootte te genereren, aangezien voor matrixvermenigvuldiging het aantal kolommen in array1 (7) gelijk moet zijn aan het aantal rijen in array2 .
De functie COLUMN retourneert de matrix met 7 kolommen (3,4,5,6,7,8,9). Door deze array te verhogen tot een macht van nul, krijgen we een 7 x 1 array zoals (1,1,1,1,1,1,1), die TRANSPOSE verandert in een 1 x 7 array zoals (1; 1 ; 1; 1; 1; 1; 1).
MMULT wordt vervolgens uitgevoerd en retourneert een 10 x 1 matrixresultaat (2; 0; 0; 3; 0; 0; 0; 1; 0; 0), dat wordt verwerkt met de logische uitdrukking> = 2, resulterend in een matrix van TRUE FALSE waarden:
(TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE).
We dwingen opnieuw TRUE FALSE naar 1 en 0 met een dubbel negatief om een laatste array binnen SUM te krijgen:
=SUM((1;0;0;1;0;0;0;0;0;0))
Wat correct 2 retourneert, het aantal namen met minstens 2 scores onder de 70.
