Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
19.04.2024 15:39:42
Exceltabelle daten per combobox zu word Datei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1912to1916
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
1912to1916
1912to1916
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summenprodukt exkl. ausgeblendeter Werte

Summenprodukt exkl. ausgeblendeter Werte
21.12.2022 09:59:05
Chris
Hallo Zusammen,
ich habe eine Frage zu einer Formel in dem File hier in C17 / D17:
https://www.herber.de/bbs/user/156875.xlsx
Ich möchte in Zeile 17 Gesamtwerte darstellen, dazu arbeite ich für den Summenteil mit Aggregat, es werden dann ausgeblendete Zeilen nicht summiert. Das passt.
Mein Problem ist dann der Multiplikationsteil: Hierfür verwende ich Summenprodukt um die Werte aus Spalte C mit den jeweiligen Werten der Spalte D zu multiplizieren.
Allerdings funktioniert das nicht wenn Zeilen ausgeblendet werden da immer alle Zahlenpaare multipiziert werden, auch ausgeblendete.
In Zelle D17 habe ich den Formelteil aus C17 separat kopiert um das Problem zu isolieren.
Wie bekommt man es hin dass das Summenprodukt nur für die sichtbaren und nicht ausgeblendeten Werte errechnet wird?
Danke für euer Feedback und viele Grüße
Chris

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Nimm ne Zusatzspalte als Filterbedingung
21.12.2022 10:06:54
lupo1
... dann erschlägst Du zwei Fliegen mit einer Klappe.
AW: Summenprodukt exkl. ausgeblendeter Werte
21.12.2022 10:09:41
Daniel
HI
das einfachste:
1. Hilfsspalte hinzufügen mit der Formel: =Teilergebnis(103;A2)
(A2 ist eine Zelle mit Inhalt in der selben Zeile)
Das Teilergebnis ist dann 1, wenn die Zelle Inhalt hat und eingeblendet ist, ansonsten 0
2. nimm dann im Summenprodukt die Bedingung mit hinzu: (Hilfsspalte = 1)
Gruß Daniel
So ein Murks
21.12.2022 10:15:03
lupo1
Du nimmst eine inferiore Autofilterbedingung und danach flickst Du an ihr mit TEILERGEBNIS rum.
Nicht mit Lupo.
AW: So ein Murks
21.12.2022 15:26:17
Chris
Hallo Zusammen,
danke fürs Feedback. Leider kann ich nur sehr erschwert mit einer Hilfsspalte hier arbeiten, das Beispiel ist reduziert, ich bräuchte über 15 Hilfsspalten weil es unterschiedliche Zeiträume und Fakoren in unterschiedlichen Spalten gibt.
Kann man "Summenprodukt" nicht modifizieren oder hat jemand noch eine weitere Idee in der Beispieldatei?`
Danke fürs Feedback,
Chris
Anzeige
AW: So ein Murks
21.12.2022 16:37:09
Yal
Hallo Chris,
wenn Du nur eine einzige Hilfespalte haben möchte, dann verwende eine User Defined Function. Diese wird wie eine ExcelFormel im Blatt verwendet:
_ Öffen VBA-Editor (Alt+F11)
_ Füge einen Module ein: Einfügen, Modul
_ Füge dort folgende Code ein (wenn Du versuchst, den Code zu lesen und diesen auf deutsch zu bringen, wirst du merken, dass VBA keine Hexenwerk ist)

Public Function IstSichtbar(ByVal Target)
IstSichtbar = Not Target.EntireRow.Hidden
End Function
_ Füge in G9:G15 und drunter (zumind. in deinem Beispiel)
= IstSichtbar(A9)
und in G17
=SUMMENPRODUKT(C9:C15;D9:D15;G9:G15)
VG
Yal
Anzeige
Vergiss den UDF
21.12.2022 18:34:44
Yal
nimm =teilergebnis(103;A2)
Scho wida was g'lernt. Der heutige Glühwein wird auf Lupos Wohl angestossen.
VG
Yal
nee, auf Daniel. Ich war gegen TEILERGEBNIS
21.12.2022 19:52:38
lupo1
Ach so
22.12.2022 12:46:22
Yal
Schon wieder vertan :-)
Was ich am der Lösung 0 bei versteckte, 1 bei eingeblendete mag, sei es mit UDF oder Teilergebnis, ist dass diese Lösung Aufgaben-neutral ist, und somit leicht auf abweichenden Problemstellung übertragbar.
Kompliziertere Lösungen, die nur in einem präzisen Kontext funktionieren, werde ich mir nicht merken können (oder wollen).
VG
Yal
AW: Vergiss den UDF
21.12.2022 20:16:55
Chris
Hallo Yal,
danke für den Lösungsansatz den ich mir auch ansehen werde, Werner hat jedoch eine Modifizierung auf Summenprodukt geschickt die sofort und ohne Hilfsspalte zum Ziel führt, hier sein Ansatz: =SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("D"&ZEILE(B9:B15)))*C9:C15)
Viele Grüße
Chris
Anzeige
Warum mehrere Hilfsspalten?
21.12.2022 18:10:27
Daniel
Du solltest nur eine einzige Hilfsspalte dieser Art in der Tabelle benötigen.
Innerhalb der selben Tabelle gibt es ja nur einen Filter und der ist für alle gleich.
Das Ergebnis ist ja 1 (sichtbar) oder 0 (unsichtbar).
Die Hilfsspalte nimmst du dann als dritten Parameter in dein Summenprodukt mit auf. Summenprodukt(D7:D15;E7:E15;G7:G15)
Diese Hilfsspalte kann für alle Auswertungen in dieser Tabelle verwendet werden.
Nur wenn du mehrere Tabellen hast, brauchst du für jede Tabelle eine Hilfsspalte.
Gruß Daniel
AW: ohne Hilfsspalte mit Hilfe von INDIREKT() ...
21.12.2022 19:24:02
INDIREKT()
Hallo Chris,
... so: =SUMMENPRODUKT(TEILERGEBNIS(9;INDIREKT("D"&ZEILE(B9:B15)))*C9:C15)
Gruß Werner
.. , - ...
Anzeige
AW: ohne Hilfsspalte mit Hilfe von INDIREKT() ...
21.12.2022 20:13:35
INDIREKT()
Lieber Werner,
vielen Dank, das ist klasse, die Modifizierung funktioniert perfekt und ohne Hilfsspalten und liefert das erwartete Ergebnis!
Viele Grüße
Christian
AW: bitteschön owT
22.12.2022 08:48:35
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige