Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 10:23:52
Tabelle 90° drehen + Filteroptionen
23.04.2024 10:11:03
Dateiname plus Wenn vorhanden dann "(1)" anhängen.
23.04.2024 09:45:43
mehrere Bedingungen verknüpfen und mehr
23.04.2024 08:59:38
Bedingte Formatierung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1884to1888
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1884to1888
1884to1888
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(

SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(
21.06.2022 05:53:51
Fred
Hallo Excel Profis,
ich erhalte einen Fehler (#BEZUG!) bei folgender Formeleingabe:

=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(Tabelle2[Anfang]))*(Tabelle2[b]>1))
Es sollen in einer gefilterten Tabelle (Tabelle2) die Werte aus Titelspalte "Anfang" summiert werden, wenn in gleicher Tabelle (Titelspalte: "Ende") der Wert über 1 liegt.
Leider ist die Formel fehlerhaft. Kann mir bitte jemand dieses korrigieren.
Gruss
Fred

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(
21.06.2022 07:08:53
Fred
Moin Steve,
hatte zwichenzeitlich die Spaltentitel geänder,- aber selbst

=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(Tabelle2[Anfang]))*(Tabelle2[Ende]>1))
ergibt Fehlerwert.
Mit der Formel von https://www.herber.de/excelformeln/src/call.pl?idx=386
in der die Zeilennummern genannt werden, klappt es. - Warum nicht wie oben mit der Angabe von Tabellennamen und Spaltentitel ?
Gruss
Fred
Anzeige
AW: (D)eine (Beispiel)datei könnte aufklären owT
21.06.2022 07:52:51
neopa
Gruß Werner
.. , - ...
AW: (D)eine (Beispiel)datei könnte aufklären owT
21.06.2022 08:08:37
Fred
Hallo Werner,
mit "Summenprodukt" & "Indirekt" hatte ich schon immer meine Schwierigkeiten ..
Hier mal ein simples Beispiel
https://www.herber.de/bbs/user/153686.xlsb
Gibt es nicht im Netz eine Seite, wo eine entsprechende Funktion (Summe mit Bedingung bei gefilterten Daten) downloadbar ist? - Man nur neben dem Fuktionsnamen Summenbereich, Kriterienbereich und Kriterium eintragen muss? :-)
Gruss
Fred
AW: passe die Formel doch einfach dem an ...
21.06.2022 08:33:14
neopa
Hallo Fred,
... z.B. so: =SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("B"&ZEILE(Tabelle2[Anfang])))*(Tabelle2[Ende]=10))
Gruß Werner
.. , - ...
Anzeige
AW: passe die Formel doch einfach dem an ...
21.06.2022 08:38:52
Fred
Ja, Werner,
so funktioniert es!
Ich werde diese Formel als "Referenz" speichern,- weil immer wieder gebraucht.
Vielen Dank!!
Gruss
Fred
AW: ich würde es jedoch so nicht tun owT
21.06.2022 08:44:23
neopa
Gruß Werner
.. , - ...
Weil Werner...
21.06.2022 09:35:02
{Boris}
Hi,
...grundsätzlich was gegen volatile Funktionen (hier: INDIREKT) hat - das geht schon fast in Richtung Phobie ;-))
Ich halte das hingegen - für überschaubare Datenmengen - für völlig ok - und ich glaube, Werner letztlich auch ;-)
VG, Boris
Anzeige
AW: Weil Werner...
21.06.2022 10:40:39
Luschi
Hallo {Boris],
das Problem bei dieser Formel ist das statische "B" für die Spalte, denn die wandert nicht mit, wenn neue Spalten davor eingefügt bzw. gelöscht werden. Wie man das umgeht, siehe mein Beitrag etwas tiefer.
Gruß von Luschi
aus klein-Paris
Ich bin mal gespannt, wann die 1. Gerüchte auftauchen, daß die neue Array-Kultur in E_365 auch nur volatile ist, d.h. beliebige Zelländerung und schon rauschen die neuen Formel durch die Rechenmaschine.
INDIREKT und relative Adressierung
21.06.2022 10:57:13
{Boris}
Hi Luschi,
...dann würde ich aber auf die Z1S1-Schreibweise bei INDIREKT ausweichen - so in der Art (kann man alles noch verfeinern):
=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("Z"&ZEILE(Tabelle2[Anfang])&"S(-6)";))*(Tabelle2[Ende]=10))
VG, Boris
Anzeige
AW: INDIREKT und relative Adressierung
21.06.2022 11:07:12
Daniel
Hi
die A1-Schreibweise hat den Vorteil, dass sie auch international funktioniert.
in einem englischen Excel müssten für Indirekt Z und S durch R und C ausgetauscht werden, was hier nicht automatisch funktioniert, da sie sich innerhalb von Texten befinden.
Gruß Daniel
Das ist halt der Kompromiss....
21.06.2022 11:15:32
{Boris}
Hi,
...den man bei festverdrahteten Koordinaten eingeht. Aber sollte man natürlich wissen, dass das dann nur lokal gilt - insofern korrekter Hinweis!
VG, Boris
AW: Das ist halt der Kompromiss....
21.06.2022 11:35:03
Daniel
wenn man hier auf die formatierte Tabelle referenzieren will, kann man die Funktion ADDRESSE benutzen, um den Text für Indirekt mit A1-Addressen zu erstellen, damit ist die Formel dann unabhängig von der Lage der Tabelle im Tabellenblatt:
INDIREKT(ADRESSE(ZEILE(Tabelle1[Spalte1]);SPALTE(Tabelle1[Spalte2])))
aber wie ich schrieb, für solche Auswertungen tut man sich leichter, wenn man das Teilergebnis als zusätzliche Spalte zur Tabelle hinzufügt, dann man man einfach und unkompliziert mit SummeWenns auswerten.
Gruß Daniel
Anzeige
AW: das ist hier nicht der (alleinige) Grund owT
21.06.2022 11:07:46
neopa
Gruß Werner
.. , - ...
AW: weil ...
21.06.2022 11:20:52
neopa
Hallo Fred,
... aus Deiner Datei ist nicht eindeutig ersichtlich, warum in [Anfang] stets eine 1 in der Zeile steht, wo in [Ende] eine 10 steht und ich INDIREKT() möglichst vermeide bzw. nur "mit Handschuhen anfasse".
Was spricht denn gegen eine einfache zusätzliche Hilfsspalte. gerade bei "formatierten Tabellen" wie Deiner ist eine solche doch sehr einfach und pflegeleicht und nützlich. Also ich hab in Deiner Datei in B5 "Hilfssp" eingeschrieben und in B6: =TEILERGEBNIS(102;[@Ende])
Dann ist das von Dir angestrebte Ergebnis bei beliebiger Filterung mit der einfachen Formel:
=ZÄHLENWENNS(Tabelle2[HilfsSp];1;Tabelle2[Ende];10) ermittelt. Die "Hilfssp" kann auch für zusätzliche andere Auswertungen sehr dienlich sein.
Gruß Werner
.. , - ...
Anzeige
AW: passe die Formel doch einfach dem an ...
21.06.2022 09:07:04
Luschi
Guten Morgen Werner,
für den Fall, das die IT spaltenmäßig verschoben wird durch Einfügen oder Löschen von Spalten kann man auch so vorsorgen:

=SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(WECHSELN(ADRESSE(1;SPALTE(Tabelle2[Anfang]);4);"1";"") &ZEILE(Tabelle2[Anfang])))*(Tabelle2[Ende]=10))
Gruß von Luschi
aus klein-Paris
PS: den Zusatzteil habe ich hier gefunden:
https://excelhero.de/formeln/spaltennummer-in-buchstaben-umwandeln/#:~:text=Überblick,und%20baut%20daraus%20einen%20Bezug.
Anzeige
AW: auch so würde ich es hier nicht vornehmen ...
21.06.2022 11:11:15
neopa
Hallo Luschi,
... ersetze doch einfach mal z.B. in B8 die 1 durch z.B. eine 3.
Gruß Werner
.. , - ...
AW: (D)eine (Beispiel)datei könnte aufklären owT
21.06.2022 08:16:22
Fred
.. evt mit "AGGREGAT" ?
AW: SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT(
21.06.2022 09:16:04
Daniel
Hi
Indirekt benötigt eine Text, der wie ein Zellbezug aussieht um diesen Text dann in einen Zellbezug umwandeln zu können.
wenn du Indirekt schon mit einem Zellbezug über mehrere Zellen fütterst, kann das nicht funktionieren.
Du brauchst hier in einer Matrixformel Einzelergebnise, dh Teilergebnis muss über die Schleife mit Einzelzellen befüllt werden, sobald es einen Zellbereich bekommt, wird es diesen Zellbereich als Gesamtes Auswerten und zu einem Einzelwert verdichten, das ist in dieser Formel aber noch zu früh, da das Zusammenfassen zu einem Einzelwert erst im Summenprodukt erfolgen darf.
ich würde hier so vorgehen:
1. füge der Tabelle eine weitere Spalte hinzu mit der Formel: =TEILERGEBNIS(3;[@Anfang]) ,als ein Teilergebnis auf eine einzige Zelle mit Inhalt in der gleichen Zeile. Das Ergebnis wird 1 für sichtbare und 0 für ausgeblendete Spalten sein.
2. Verwende dann diese Spalte als zusätzliches Kriterium im SummeWenns: 

=SUMMEWENNS(Tabelle2[Anfang];Tabelle2[Ende];">1";Tabelle2[Spalte1];1)
oder auch im Summenprodukt:

=SUMMENPRODUKT(Tabelle2[Anfang];(Tabelle2[Ende]>1)*Tabelle2[Spalte1])
Spalte1 ist die Spalte mit der Teilergebnisformel.
aufgrund der oben gezeigten Problematik ist es hier wesentlich sinnvoller, mit der Hilfsspalte zu arbeiten als den Zustand des Ausgeblendet seins in der Auswerteformel zu ermitteln. Hinzu kommt, dass das nur mit Indirekt funktioniert, welches aber volatil ist und dafür sorgt, dass deine Auswerteformel jedes mal neu berechnet wird, wenn sich irgendwo in irgendeiner geöffneten Exceldatei irgendein Wert ändert. Wenn die Tabellen größer sind und die Auswerteformel zur Berechnung eine spürbare Rechenzeit hat, kann das lästig sein.
Gruß Daniel
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
2
2