Summenprodukt Dynamisch

[Tiefgekühlt]

Hallo zusammen,

ich verzweifle gerade an einer Excelformel...  

Ich möchte aus einer gefilterten Tabelle die Anzahl der Zellen mit einem bestimmtem Wert(C2) Zählen. Soweit so gut.
Jetzt kann sich die Spalte, in der diese Werte (C2) stehen, ändern und da brauche ich eure Hilfe. Meine aktuelle formel lautet:

=SUMMENPRODUKT(TEILERGEBNIS(103;INDIREKT("Tabelle1!"&O16&""&ZEILE(1:20000)))*(Tabelle1!I1:I20000=C2))

Der vordere Teil ist inwischen dynamisch (Excel nimmt den Spaltenbuchstaben aus O16) bei dem hinteren Teil nach dem * habe ich noch Probleme. Das I möchte ich auch am besten mit der Zelle O16 ersetzen.

Danke schonmal :)

[Jonas0806]

Hallo,

das geht grundsätzlich sicher performanter. Lade mal bitte eine Beispieldatei hoch.

[bayernsandy]

Hallo Tiefgekühlt!
Basierend auf deiner Lösung, in die du dich bereits reingearbeitet hast, habe ich die Formel einfach mal weiterentwickelt.
Beispieldaten:  siehe angehängte Hardcopy in jpg Format


G1 enthält den zu suchenden Wert.
G2 den zu durchsuchenden Bereich
Formel in B13:    
=SUMMENPRODUKT((TEILERGEBNIS(3;INDIREKT("B"&ZEILE(INDIREKT(G2))))=1)*(INDIREKT(G2)=G1))

Gruß Thomas

[Tiefgekühlt]

Ja das kann gut sein :)

hier eine Beispieldatei

[Der Steuerfuzzi]

Hallo,

leider befinden sich in Deiner Beispieldatei keine gefilterterten Daten :22:

Grundsätzlich würde ich INDIREKT vermeiden und durch eine Kombination aus INDEX und VERGLEICH ersetzen. Das hat neben der Volatilität von INDIREKT auch noch den Nutzen, dass Du Dir die umständliche Spaltenermittlung sparen kannst.

Noch ein Hinweis zur Beispieldatei: Diese sollte so nah wie möglich an Deinem Original-Problem liegen und auch das Wunschergebnis beinhalten.

[Tiefgekühlt]

@Thomas: Danke das werd ich gleich mal ausprobieren. :)

@Steuerfuzzi

Also in Tabelle1 sind die Daten wo dann auch gefiltert wird (zB. nach A).
In dem Blatt Statistik soll dann die Anzahl der Werte angegeben werden( zB. gibt es in der Zeile mit Cola 12 mal)

[Der Steuerfuzzi]

OK, verstanden. Ja, dann muss ich meine Aussage anpassen. Durch den Filter musst Du die Zeilen mit Teilergebnis einzeln abarbeiten und das mW geht nicht ohne INDIREKT. Also kann man das in diesem Fall wohl nicht durch INDEX/VERGLEICH ersetzen.

[Jonas0806]

Hallo,

mit VBA wäre das kein Problem, ist das eine Möglichkeit?

[Der Steuerfuzzi]

er mein Vorschlag:

Code:

C2 =SUMMENPRODUKT(TEILERGEBNIS(103;INDIREKT("Tabelle1!G"&ZEILE($A$2:INDEX($A:$A;VERWEIS(2;1/(Tabelle1!$G$1:$G$200<>"");ZEILE(Tabelle1!$G$1:$G$200))))))*((INDEX(Tabelle1!$A:$AA;2;VERGLEICH($O$12;Tabelle1!$1:$1;0)):INDEX(Tabelle1!$A:$AA;VERWEIS(2;1/(Tabelle1!$G$1:$G$200<>"");ZEILE(Tabelle1!$G$1:$G$200));VERGLEICH($O$12;Tabelle1!$1:$1;0)))=C2))

Das ganze funktioniert für die letzte Spalte (no entry) nur, wenn Du die Spaltenüberschrift in H2 entfernst (H2 muss leer sein!). Wenn Du die Überschrift unbedingt willst, kannst Du die Formel in H3 so abändern, das statt dem Vergleich mit H2 ein Vergleich mit einem Leerstring "" stattfindet.

Die Formel berücksichtigt die tatsächliche Länge der Liste und sollte damit schneller sein, als Deine Formel, die einfach bis Zeile 20000 vergleicht.

[Hydronaut]

Noch etwas schneller ist die Pivot-Tabelle.
Voraussetzung ist dass die Tabelle in eine Datensatzliste umgewandelt wird.
"Objekt" in Filterbereich und auf "A" filtern.
"Getränk" und "Wert" in den Spaltenbereich.
"Wert" in den Wertebereich.