Generische Formel
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Zusammenfassung
Um sichtbare Zeilen nur mit Kriterien zu zählen, können Sie eine ziemlich komplexe Formel verwenden, die auf SUMPRODUCT, SUBTOTAL und OFFSET basiert. In dem gezeigten Beispiel lautet die Formel in C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Vorwort
Die SUBTOTAL-Funktion kann auf einfache Weise Summen und Zählungen für ausgeblendete und nicht ausgeblendete Zeilen generieren. Es ist jedoch nicht in der Lage, Kriterien wie COUNTIF oder SUMIF ohne Hilfe zu verarbeiten. Eine Lösung besteht darin, SUMPRODUCT zu verwenden, um sowohl die SUBTOTAL-Funktion (über OFFSET) als auch die Kriterien anzuwenden. Die Details dieses Ansatzes werden unten beschrieben.
Erläuterung
Im Kern funktioniert diese Formel, indem zwei Arrays in SUMPRODUCT eingerichtet werden. Das erste Array wendet Kriterien an und das zweite Array behandelt die Sichtbarkeit:
=SUMPRODUCT(criteria*visibility)
Die Kriterien werden mit einem Teil der Formel angewendet:
=(C5:C8=C10)
Welches erzeugt ein Array wie folgt:
(FALSE;TRUE;FALSE;TRUE)
Wobei WAHR bedeutet "Kriterien erfüllt". Beachten Sie, dass die TRUE FALSE-Werte durch die mathematische Operation automatisch in Einsen und Nullen konvertiert werden, da wir für dieses Array die Multiplikation (*) verwenden.
(0;1;0;1)
Der Sichtbarkeitsfilter wird mit SUBTOTAL mit der Funktionsnummer 103 angewendet.
SUBTOTAL kann versteckte Zeilen beim Ausführen von Berechnungen ausschließen. In diesem Fall können wir damit einen "Filter" generieren, um versteckte Zeilen in SUMPRODUCT auszuschließen. Das Problem ist jedoch, dass SUBTOTAL eine einzelne Zahl zurückgibt, während wir eine Reihe von Ergebnissen benötigen, um sie erfolgreich in SUMPRODUCT zu verwenden. Der Trick besteht darin, OFFSET zu verwenden, um SUBTOTAL eine Referenz pro Zeile einzugeben, sodass OFFSET ein Ergebnis pro Zeile zurückgibt.
Dies erfordert natürlich einen weiteren Trick, der darin besteht, OFFSET ein Array zu geben, das eine Zahl pro Zeile enthält, beginnend mit Null. Wir machen das mit einem Ausdruck, der auf der ROW-Funktion basiert:
=ROW(C5:C8)-MIN(ROW(C5:C8)
Dadurch wird ein Array wie folgt generiert:
(0;1;2;3)
Zusammenfassend wird das zweite Array (das die Sichtbarkeit mit SUBTOTAL behandelt) wie folgt generiert:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Und schließlich haben wir:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Welches gibt 1 zurück.
Mehrere Kriterien
Sie können die Formel erweitern, um mehrere Kriterien wie folgt zu behandeln:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Ergebnisse summieren
Um eine Summe von Werten anstelle einer Zählung zurückzugeben, können Sie die Formel so anpassen, dass sie einen Summenbereich enthält:
=SUMPRODUCT(criteria*visibility*sumrange)
Die Kriterien und Sichtbarkeitsarrays funktionieren wie oben erläutert, mit Ausnahme von Zellen, die nicht sichtbar sind. Wenn Sie eine teilweise Übereinstimmung benötigen, können Sie einen Ausdruck mit ISNUMBER + SEARCH erstellen, wie hier erläutert.
