Wer kann mir bei der Formel summenprodukt in Zelle G4 helfen?
Ich habe in Spalte F eine Formel mit Summenprodukt, welche ich kopiert und in Spalte G eingefügt habe. Nachdem ich die Spalte in der Formel angepasst habe, kommt mir leider immer nur die Auswertung #WERT! Die abgebildete Formel ist jene aus der Zelle G4. Die selbe Formel habe ich in Zelle F4 drin, jedoch mit der Spalte E statt I. Kann das sein weil ich ab Zelle I18 die Formel: =WENN(E18>0;1;"") stehen habe? Das Ganze ist noch schwierig zu erklären. Wäre einfacher, wenn man die Datei statt einem Bild mitliefern könnte.
Vielen Dank schon mal für eure Hilfe.
4 Antworten
Hallo,
nur um schon mal einem eventuellen Fehler vorzubeugen, willst du die Auswertung tatsächlich nur auf den Monat? Oder ist vielmehr Monat und Jahr ausschlaggebend.
Auch wenn es nicht funktioniert, würde dein Versuch ja momentan auch das Jahr 2018 auswerten, sobald es dafür Daten gibt.
Wenn es um den Monat und Jahr geht, würde ich die Abfrage über die Range mit Teilabfragen >= und <= machen.
Also: =Summenprodukt((D18:D490>=Datum(2017;1;1)*(D18:D490<=Monatsende(Datum(2017;1;1);(I18:I490))
LG, Chris
Anmerkung: Abfrage Monatsende im zweiten Teil macht dann Sinn, wenn Jahr und/oder Monat sich aus Zellen ziehen... dann muss man den letzten Tag des Monats nicht manuell anpassen.
ja da hast du recht wegen dem Jahr, danke dir für deinen Tipp
Hey Chris, kannst du dir die Formel nochmal anschauen?
Klappt irgendwie nicht ;D
Kann es außerdem sein das du eine Monatsangabe bei MONATSENDE() vergessen hast?
=SUMMENPRODUKT(($D$18:$D$192>=DATUM(2017;1;1)*($D$18:$D$192<=MONATSENDE(DATUM(2017;1;1);1)))*($J$18:$J$192))
So hab ich die Formel eigentlich verstanden, als Ergebnis werden aber ALLE zusammengezählt nicht nur die was auf den Array zutreffen!
hmmm, Erholung vom Urlaub ist wohl schon wieder weg...
ich schau morgen mal, aber zumindest dem Monatsende-Ding hast du natürlich schon mal recht.
so, jetzt noch mal richtig, wie ich es eigentlich wollte.
=SUMMENPRODUKT(($D$18:$D$192>=DATUM(2017;1;1))*($D$18:$D$192<=MONATSENDE(DATUM(2017;1;1);1));($J$18:$J$192))
Prinzipiell könnte man aber auch mit einer Summewenns rangehen...
=SUMMEWENNS($J$18:$J$192;$D$18:$D$192;">="&"01.01.2017";$D$18:$D$192;"<="&"31.01.2017")
LG, Chris
Probier einmal, in der Formel anstelle bis zur Zeile 490 zu rechnen, nur bis 30 oder so zu rechnen. Reduzier die Zeilenzahl mal auf ein Minimum.
Ich vermute, eine Zelle der Spalte enthält einen nichtnumerischen Wert.
Danke, ja, wenn ich bis Zeile 30 rechne, ist alles super. Dann suche ich jetzt mal die verflixte Zelle. Vielen Dank, bin wirklich fast verzweifelt. Hast du grad noch einen Typ wie ich die am schnellsten finde?
Was du sehr oft anwenden kannst ist das Bisektionsverfahren.
Du prüfst eine Hälfte, ob dort der Fehler auftritt. Wenn ja, halbierst du diese Hälfte und suchst in einer von beiden, wenn nein, dann halbierst du die andere Hälfte und testest die Formel auf einer der beiden.
Ein kleines Bisschen besser ist es, wenn du die Datenmenge im Goldenen Schnitt teilst, aber da Halbieren in der Praxis einfacher ist, würde ich die Testintervalle halbieren.
Du kannst natürlich auch in einer Hilfsspalte auf ISTZAHL prüfen oder ähnliches.
@Suboptimierer, weißt du von einem Problem der SUMMENPRODUKT()-Funktion bei leeren Ergebniszellen?
Als Beispiel gerade die Tabelle hier aus der Frage:
Datum von A1:A200 eingetragen.
Ergebniszellbreich ist einfach mal J1:J200
So, in J160 steht jetzt nichts drin, bis dahin funktioniert die Formel, sobald aber J160 mit eingeschlossen wird kommt der Fehler #WERT
Komisch ist aber, wenn die Zelle 160 jetzt mit einem Wert befüllt wird, funktioniert die Formel, auch wenn dieser Wert dann wieder entfernt wird!
Formel:
=SUMMENPRODUKT((MONAT($A$1:$A$200)=MONAT(1)*1)*$J$1:$J$200)
Das Bisektionsverfahren hab ich angewandt :) Geht recht schnell, auch bei großen Datenmengen!
Es scheint offensichtlich ein Konvertierungsproblem vorzuliegen.
Man könnte es mit ...*($J$1:$J$200+0) versuchen.
Vielleicht sind dort unsichtbare Zeichen enthalten, z. B. Leerzeichen.
Der Fragesteller sollte schnell herausfinden können, woran es liegt (Inhalt / Formatierung / ...) Er kennt ja schon die Stelle.
Hab die Tabelle gesehen! Hab nichts davon entdecken können :D
Wie wäre es wenn du deine Tabelle (zumindest den kleinen Teil) per DropBox bereit stellst?
Habe die Tabelle mal in klein nachgestellt und bei mir klappt alles mit der selben Funktion wie bei dir!
Also 1:1!
Wie werden die Daten denn generiert? Sind die händisch hinterlegt?
Grüße,
iMPerFekTioN
Die Daten bis und mit Spalte E (ab Zeile 18) sind manuell eingegben, Spalte H hat die Formel =WENN(D18=0;"";D18) und Spalte I die Formel =WENN(E18>0;1;""). Denke es liegt an der Formel aus Zelle z.B. I18 oder? Habe grad noch bemerkt, dass ich die Summe aus der Spalte J und nicht die aus Spalte I brauche, geht aber auch nicht.
Nein liegt es nicht :)
Habe die Formeln so übernommen wie von dir geschildert :p
...und es klappt ohne Probleme!
Versuch mal die Formatierung der Zelle zu löschen Rechtsklick->Formatierung entfernen und die Funktion nochmal ein zu geben!
Eventuell hat sich ein Formatfehler in den Zelleigenschaften eingeschlichen?
wenn ich darf würde ich dir gerne die Datei in Dropbox freigeben
Also, folgendes!
Du hast in deiner Tabelle mehrere Fehler :)
SUMMENPRODUKT reagiert sehr empfindlich auf 'WERT Fehler und andere Dinge!
In deiner Tabelle gibt es ab Zeile 109 ein Fehler:
SUMMENPRODUKT() kommt mit den leeren Zellen nicht klar!
Deswegen mein Rat: ersetze bitte in Spalte J die Formel:
=WENN(I18=1;I18*20-F18;"")
durch diese:
=WENN(I18=1;I18*20-F18;0)
So, weiter unten hast du dann noch das Problem, dass dann eine Zelle mit 'WERT ausgefüllt wird, weil du eine Abfragezelle mit Text befüllt hast.
Das ist die Zelle F154!
Das solltest du vll. woanders markieren :)
So weiter gehts! Du hast angegebene du willst SUMMENPRODUKT() von J18:J490) verwenden!
Wenn du ab J193 keine Werte mehr eingetragen hast, die genutzt werden, bekommst du den Fehler #WERT
Trage einmal die Formel in J18 ein und ziehe diese bis J900 runter:
=WENN(I18=1;I18*20-F18;"")Die Zelle F154 löschst du dann bitte noch und alles funktioniert :)
Grüße,
Hi, bist ein Schatz, vielen lieben Dank und was meinst du zu dieser Formel von Chris
=Summenprodukt((D18:D490>=Datum(2017;1;1)*(D18:D490<=Monatsende(Datum(2017;1;1);(I18:I490))
Es fehlen am Schluss zwar 3 Klammern aber es gibt mir auch den gleichen Fehler aus, stimmt scheinbar was nicht. Die Überlegung der zusätzlichen Jahresabfragung finde ich aber gut
Die Formel von Chris ist super :) Auch sein Gedankengang ist sehr gut! Aber zu der Formel solltest du lieber Ihn Fragen :p
Klappt das denn mit meinen Änderungsanmerkungen?
Zumal dem, wollte ich gleich mal kurz Fragen woher der Name Käser in deiner Liste kommt :p
*grins* So heiß ich nämlich auch :)
ja, deine Anmerkungen haben super geklappt. Danke dir, ich hätte noch eine andere Liste, die ich dir gerne per Mail schicken möchte, wenn ich darf. Da kann ich dir mein Problem dann beschreiben.
Die Liste von vorhin mache ich für eine Freundin und ein Arbeitskollege von ihr heisst Käser. Den Namen gibt es öfter bei uns in der Schweiz.
Ahhaaa jetzt aus der Schweiz also :)
Ja ich komm aus Deutschland, witzig das es den Name so oft in der Schweiz gibt! Wusste ich nicht!
Natürlich darfst du - nur her damit!
Welche Zahlen musst du denn Addieren und sind diese "" von alleine drinnen oder hast du sie da reingefügt?
Na das was in deiner Frage steht. Sieh mal nach dann weisst du was ich meine. Oder ist das ein Schreibfehler?
Ich bin nicht der Fragensteller, falls dir das entfallen ist :)
Aber ich sehe nichts von "" in der Tabelle!
Nur in den Formeln und dort werden "" als Platzhalter für leere Zellen genutzt!
Upps sry. Bin ein bisschen Müde. Ach also ich habe es anders gelernt.
Anna was verstehst du nicht :) ich erklärs dir gerne aber Frag bitte nach und erklär was du nicht verstehst :)
Ich habe es eben anderes erklärt bekommen in der Schule. Ich habe es so erklärt bekommen : Wenn... X ist Dann...X und dann irgendetwas anderes.
Ach jetzt :)
Das ist alles richtig! Genau so stimmt es!
DU kannst aber mit "" angeben, dass wenn etwas nicht zutrifft, die Zelle leer bleibt!
"" steht für leeren Text
Wenn du z.B. diese Funktion hast:
=WENN(A1=2;"Es ist eine 2";"Es ist keine 2"
In der Zelle steht "Es ist eine 2" wenn in A1 eine 2 eingetragen wird!
Wenn das NICHT der Fall ist steht in der Zelle "Es ist keine 2"
Hier wird Text mit Hilfe von Anführungszeichen hinterlegt! Wenn du die Anführungszeichen nicht mit Text befüllt, kommt also kein Text und somit eine leere Zelle raus!
DU merkst, du hast nur nicht verstanden wofür die "" da sind und ich hab dich evtl. auch etwas durcheinander gebracht!
Also sorry dafür :) Das was du denkst ist richtig! Nur die Erklärung für die "" hat dir gefehlt :)
Ja danke für dein Verständnis, dass ich dort verwirrt war.
Dann kann ich da eigentlich nicht weiter helfen. Echt schade.
Du konntest dir selbst helfen :) und das zählt! Du kommst weiter und hilfst anderen!
Ich wette, dass mindestens noch 200 andere Menschen irgendwann dasselbe Fragen, deine Antwort lesen und durch unser Gespräch hier auf eine Erklärung/Lösung kommen!
Siehst du? du hast geholfen :p
Schönen Tag dir noch :)
Aus diesem Blickwinkel habe ich es mir noch nicht angesehen. :) Danke gleichfalls.
Hallo Chris. Danke für deinen Tipp mit der Jahresabfrage. Wenn ich jedoch deine Formel einsetze, habe schon alles was mir
iMPerFekTioN angegeben hat geändert, erscheint leider der Fehler: #WERT!. Seine Änderungen haben bestens funktioniert für die Monatsabfrage. Habe auch bei deiner Formel am Ende noch die fehlenden 3 Klammern angefügt.