Generieke formule
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
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 G5:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
waarbij data het benoemde bereik B4: B12 is.
Opmerking: dit is een matrixformule 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=90)
waarbij data het benoemde bereik B4: D12 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:
(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)
Net als de originele gegevens is deze array 9 rijen bij 3 kolommen (9 x 3) en gaat deze in de MMULT-functie als array1 .
Array2 is afgeleid met:
TRANSPOSE(COLUMN(data))
Dit is het lastige en leuke deel van deze formule. De functie COLUMN wordt eenvoudig voor het gemak gebruikt als een manier om een numerieke reeks van de juiste grootte te genereren. Om matrixvermenigvuldiging met MMULT uit te voeren, moet het aantal kolommen in array1 (3) gelijk zijn aan het aantal rijen in array2 .
COLUMN retourneert de matrix met 3 kolommen (2,3,4) en TRANSPONEREN wijzigt deze matrix in de matrix met 3 rijen (2; 3; 4). MMULT wordt vervolgens uitgevoerd en retourneert een 9 x 1 matrixresultaat:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
We controleren op niet-nul-items met> 0 en dwingen opnieuw TRUE FALSE naar 1 en 0 met een dubbel negatief om een laatste array binnen SUM te krijgen:
=SUM((1;0;1;1;0;0;0;0;1))
In deze laatste array staat een 1 voor een rij waarin de logische test (data = 90) true retourneert. Het totaal dat door SUM wordt geretourneerd, is een telling van alle rijen die het getal 90 bevatten.
Letterlijk bevat
Als u moet controleren op specifieke tekstwaarden, met andere woorden, letterlijk controleren of cellen bepaalde tekstwaarden bevatten, kunt u de logica in de formule op deze pagina wijzigen om de functie ISGETAL en ZOEKEN te gebruiken. Om bijvoorbeeld cellen / rijen te tellen die "appel" bevatten, kunt u het volgende gebruiken:
=ISNUMBER(SEARCH("apple",data))
Details over hoe deze formule hier werkt.
