Herbers Excel-Forum - das Archiv

Summenprodukt soll nur Zahlen aus Spalte rechnen

Bild

Betrifft: Summenprodukt soll nur Zahlen aus Spalte rechnen
von: Peter

Geschrieben am: 03.01.2007 13:42:50
Guten Tag
In der Beispielmappe habe ich zwei Tabellen abgebildet. Ich möchte gerne aus der Quelltabelle Daten in die Zieltabelle übernehmen, wenn gewisse Bedingungen erfüllt sind.
Die jetzige Formel hat den (schwerwiegenden) Makel, dass bei Zutreffen der Bedingungen alle Werte der entsprechenden Zeile addiert werden. Ich möchte jedoch, dass nur die Zahlen des betreffenden Jahres übernommen werden. Gibt es eine Möglichkeit die Formel so zu erweitern, dass dies klappt.
Nach Möglichkeit möchte in der Formel immer den ganzen Bereich (Bereich = Bereichsname der Quelltabelle) eingeben, da dies die Lesbarkeit erhöht und in alle betroffenen Zellen der Zieltabelle die gleiche Formel eingetragen werden kann.
(Zudem möchte ich später aufgrund der Grösse der Datei diese Formel via VBA berechnen und dann nur das Ergebnis in die Zellen eintragen lassen).
Danke für eine Rückmeldung.
Peter
https://www.herber.de/bbs/user/39335.xls
Bild

Betrifft: AW: Summenprodukt soll nur Zahlen aus Spalte rechn
von: ramses

Geschrieben am: 03.01.2007 14:08:29
Hallo
Josef hat dir doch diesbezügliche eine Formel mit Beispiel gesandt.
Was funktioniert daran nicht ?
Ausserdem finde ich es "unhöflich" eine Datei ohne Erklärung anzubieten.
DU willst eine Lösung,... also sollte schon eine Erklärung in der Tabelle sein wie das ganze funktionieren soll.
Ich werde die Frage jedenfalls nicht beantworten, da ich keine Lust habe hinter die ganze Logik und den Aufbau der Tabelle zu kommen.
Vor allem macht sich hier wahrscheinlich niemand die Mühe eine Formel zu entwickeln, die dann sowieso durch eine VBA-Prozedur ersetzt werden soll.
Gruss Rainer
Bild

Betrifft: AW: Summenprodukt soll nur Zahlen aus Spalte rechn
von: Peter

Geschrieben am: 03.01.2007 14:30:12
Hallo Rainer
Sorry, ich wollte nicht unhöflich sein. Ich habe zuwenig bedacht, dass die Tabelle nicht selbstredend ist. Ich bin davon ausgegangen, dass ich dann eine bestehende Formel in VBA umsetze mit worksheetfunction, daher lohnt sich nach meinem Verständnis dieser Weg schon.
Nun noch zur Funktionsweise der Tabelle am Beispiel von Zelle H36, wo die Formel:
=SUMMENPRODUKT(((Kriterium1=$D40)*(Kriterium2=$E40)*(Kriterium4=$F40)*Bereich))
steht.
Erfüllt eine Zeile im grünen Bereich folgende Bedingungen (vgl. Zellen D40,E40,F40 worauf die Formel referenziert)?
Spalte B = Wert gleich 300
Spalte C = String gleich PR
Spalte E = String = EXI
Wenn ja, dann sollen die Werte des entsprechenden Jahres (trifft hier nur auf J6 zu) in der Zieltabelle beim entsprechenden Jahr eingetragen werden (was hier bei H40 erfolgt ist).
Das Problem meiner Tabelle ist, dass ich die Eingrenzung nicht geschafft habe, dass der Eintrag nur beim betreffenden Jahr (also nur in Spalte H) steht. Die 500 sind in allen anderen Jahren auch eingetragen.
Ich dachte mir, dass es vielleicht einen einfachen Kniff gibt, dass die Jahre noch abgeglichen werden können.
Freundlicher Gruss
Peter
Bild

Betrifft: AW: Summenprodukt soll nur Zahlen aus Spalte rechnen
von: fcs

Geschrieben am: 03.01.2007 14:45:35
Hallo Peter,
mit folgender Formelanpassung, hier am Beispiel von Zelle H40 geht es.
=SUMMENPRODUKT((Kriterium1=$D40)*(Kriterium2=$E40)*(Kriterium4=$F40)*BEREICH.VERSCHIEBEN(Bereich;0;VERGLEICH(H$36;$J$1:$X$1)-1;;1))

Für den Bereich $J$1:$X$1 mit den Jahreszahlen solltest du dann ggf. ebenfalls einen Namen festlegen, z.B. Jahr.
Ob sich diese Formel problemlos in VBA umsetzen/auswerten lässt weiss ich nicht. Möglicherweise ist es dann einfacher jeweils die Spalte des jeweiligen Jahres direkt anzusprechen mit folgender Formel, hier wieder beispielhaft für Zelle H40:
=SUMMENPRODUKT((Kriterium1=$D40)*(Kriterium2=$E40)*(Kriterium4=$F40)*J$4:J$34)

Gruß
Franz
Bild

Betrifft: AW: Summenprodukt soll nur Zahlen aus Spalte rechnen
von: Peter

Geschrieben am: 03.01.2007 15:19:03
Hallo Franz
Herzlichen Dank für dein Feedback.
Ich habe das so umgesetzt und es funktioniert.
Die Funktion bereich.verschieben muss ich mir noch mal im Detail zu Gemüte führen.
Freundlicher Gruss
Peter
 Bild
Excel-Beispiele zum Thema "Summenprodukt soll nur Zahlen aus Spalte rechnen"
Vorkommen von Zahlenreihen in Spalten Zeichenfolgen in Zahlen nach Textimport
Zahlenformatierung mit Punkt nach der 1. Ziffer Namen von TextBoxes hochzählen
Zählen formatierter Zellen Autofilter auch mit Ziffern von Zahlen
Addition der absoluten Zahlen Eingabe von positiven Zahlen erzwingen
Zufallszahlen generieren, die sich nicht wiederholen Summenformel unter Zahlenreihe eintragen