Generische Formel
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Zusammenfassung
Um Zeilen zu zählen, die bestimmte Werte enthalten, können Sie eine Array-Formel verwenden, die auf den Funktionen MMULT, TRANSPOSE, COLUMN und SUM basiert. Im gezeigten Beispiel lautet die Formel in G5:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
wobei Daten der benannte Bereich B4: B12 ist.
Hinweis: Dies ist eine Array-Formel und muss mit Control Shift Enter eingegeben werden.
Erläuterung
Die von innen nach außen arbeitenden logischen Kriterien für diese Formel lauten:
--(data=90)
wobei Daten der benannte Bereich B4: D12 sind. Dies erzeugt ein TRUE / FALSE-Ergebnis für jeden Wert in Daten, und das doppelte Negativ zwingt die TRUE FALSE-Werte auf 1 und 0, um ein Array wie das folgende zu erhalten:
(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)
Wie die Originaldaten besteht dieses Array aus 9 Zeilen mal 3 Spalten (9 x 3) und geht als Array1 in die MMULT-Funktion .
Array2 wird abgeleitet mit:
TRANSPOSE(COLUMN(data))
Dies ist der knifflige und unterhaltsame Teil dieser Formel. Die COLUMN-Funktion wird einfach zur Vereinfachung verwendet, um ein numerisches Array mit der richtigen Größe zu generieren. Um eine Matrixmultiplikation mit MMULT durchzuführen, muss die Spaltenanzahl in Array1 (3) der Zeilenanzahl in Array2 entsprechen .
COLUMN gibt das 3-Spalten-Array (2,3,4) zurück, und TRANSPOSE ändert dieses Array in das 3-Zeilen-Array (2; 3; 4). MMULT wird dann ausgeführt und gibt ein 9 x 1-Array-Ergebnis zurück:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Wir suchen nach Einträgen ungleich Null mit> 0 und zwingen TRUE FALSE erneut mit einem doppelten Negativ auf 1 und 0, um ein endgültiges Array in SUM zu erhalten:
=SUM((1;0;1;1;0;0;0;0;1))
In diesem letzten Array steht eine 1 für eine Zeile, in der der logische Test (Daten = 90) true zurückgegeben hat. Die von SUM zurückgegebene Summe ist eine Anzahl aller Zeilen, die die Nummer 90 enthalten.
Wörtlich enthält
Wenn Sie nach bestimmten Textwerten suchen müssen, dh buchstäblich prüfen müssen, ob Zellen bestimmte Textwerte enthalten, können Sie die Logik in der Formel auf dieser Seite ändern, um die Funktionen ISNUMBER und SEARCH zu verwenden. Zum Zählen von Zellen / Zeilen, die "Apfel" enthalten, können Sie beispielsweise Folgendes verwenden:
=ISNUMBER(SEARCH("apple",data))
Details zur Funktionsweise dieser Formel finden Sie hier.
