Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1708to1712
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
1708to1712
1708to1712
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summewenn? Summenprodukt?

Summewenn? Summenprodukt?
18.08.2019 13:20:20
Die
Hallo,
wer kennt sich damit aus?
In Spalten steht das Tagesdatum von 1.1. bis 31.12., daraus erstelle ich pro Tag auch die aktuelle KW.
Nun stehen in den Zeilen Einträge im Format "ABC480", wobei das 'ABC' Kürzel für Projekte, die '480' die aufgewendete Minuten sind. Am Montag kann also ABC240, am Dienstag DEF300 etc. stehen.
Ziel ist jetzt, für eine KW die Anzahl der Minuten für das Projekt ABC zu errechnen.
Ich habe es mit
=SUMMEWENN([Zeile mit KW-Einträgen];"="&[Zelle Such-KW];[Zeile mit Eingaben])
versucht. Das klappt prima, wenn das ABC nicht davor steht. Ich habe es aber nicht geschafft, den Summe_Bereich so hinzubekommen, dass er bei LINKS ABC den TEIL ab 4. Stelle addiert.
Einigermaßen verständlich? Irgendwelche Ideen?
Herzlichen Dank sagt
Die Johanna

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summewenn? Summenprodukt?
18.08.2019 13:30:58
{Boris}
Hi,
separiere die Zahl in einer Hilfsspalte mit =TEIL(A1;4;99)+0
und wende darauf dann SUMMEWENN an.
Alles andere ist zwar möglich, aber nicht wirklich sauber.
VG Boris
AW: Summewenn? Summenprodukt?
18.08.2019 13:39:01
Daniel
Hi
das geht dann nur mit SummenProdukt, weil du hier um die Summe zu bilden, den Zellwert "ABC480" erst in eine Zahl umwandeln musst.
dass geht nur mit SummenProdukt, SummeWenn(s) kann die Werte in den Spalten nur so verarbeiten, wie sie vorliegen.
musste also in etwa so gehen.
ich gehe mal davon aus, dass das Projekt immer 3-Stellig in den Zellen steht:
das Datum in Zeile 1 und die KW in Zeile 2 im Format "KW01"
die Werte dann ab Zeile 3 in den Spalte A:AD
=SummenProdukt((A2:AD2="KW01")*Wenn(Links(A3:AD30;3)="ABC";Teil(A3:AD30;4;3);0))
Gruß Daniel
Anzeige
AW: Summewenn? Summenprodukt?
18.08.2019 14:01:41
Günther
Moin,
das geht wahrscheinlich auch prima mit Stichwort: Power Query (2010/13) aka Daten | Abrufen und transformieren (2016/365). Aber ohne (anonymisierte) Muster-xlsx fange ich nicht mit Versuchen an.
Gruß
Günther
AW: Summewenn? Summenprodukt?
18.08.2019 17:20:58
Die
Hallo,
danke für die Antworten:
@Boris: habe ich auch überlegt, aber bei ca. 220 Arbeittagen und ca. 10 Projekten sind das 2.200 Hilfsspalten. Würde ich gerne vermeiden, wäre aber auf jeden Fall möglich.
@Daniel: bei der Lösung kommt #WERT heraus. Das liegt vermutlich daran, dass die 480 bei ABC480 nicht als Zahl gesehen werden. Ich habe mal '--' probiert und auch '*1', hat aber beides nicht geklappt. Noch eine Idee?
@Power Query habe ich gegoogelt, davon hatte ich noch nie gehört ;-) Das würde ich lieber vermeiden, da muss ja wohl ein Add-On oder Add-In geladen werden. Das wäre mit viel Aufwand verbunden, weil etliche Nutzer damit arbeiten.
Danke euch allen für die Ideen, vielleicht hat noch jemand einen Vorschlag?
Hofft
Die Johanna
Anzeige
AW: Summewenn? Summenprodukt?
18.08.2019 17:38:39
Günther
Nun ja Johanna,
bei 2013 ist in der Tat ein Add-In von Microsoft erforderlich (welches sich einfach per Mausklick installiert). Ab Version 2016 ist PQ bereits integriert und kann die in der oben aufgeführten Richtung (vermutlich) die Sorgen abnehmen (und noch einige mehr...). ;-)
Wie gesagt, liefere eine "vernünftige" und realitätsnahe Muster-xlsx und ich zeige dir den Weg auf. Übrigens: Wenn das Ergebnis einmal erstellt ist, liegt eine ganz normale Tabelle vor, die auch mit älteren Versionen angesehen werden kann. Nur Aktualisierung geht nicht (und natürlich nicht die ganzen anderen feinen Dinge des PQ …)
Gruß
Günther
Anzeige
AW: Summewenn? Summenprodukt?
18.08.2019 17:58:03
Daniel
HI
doch, die Summenproduktformel funktioniert schon.
ich habe allerderdings vergessen, dass durch das WENN die automatische Matrixformelerkennung nicht mehr funktioniert und du daher die Eingabe der Formel mit STRG+SHIFT+ENTER abschließen musst.
dafür kannst du dann aber einfach SUMME statt SUMMENPRODUKT als Hauptfunktion verwenden.
Gruß Daniel
das von Daniel ist eine Arrayformel
18.08.2019 18:00:48
Daniel
da kannst Du auch =SUMME(... statt =SUMMENPRODUKT(... nehmen
und #WERT erhältst Du, weil er nicht A3:AD3 sondern A3:AD30 geschrieben hat
WF
AW: das von Daniel ist eine Arrayformel
18.08.2019 18:17:29
Daniel
nein WF.
das ist nicht das Problem. Die Formel funktioniert auch über mehrere Zeilen.
ich gehe mal davon aus, dass man auch an mehreren Projekten an einem Tag arbeiten darf.
das Problem ist wirklich nur, dass man aufgrund der WENN-Funktion auch mit Summenprodukt die Formel mit STRG+SHIFT+ENTER abschließen muss, da WENN ansonsten die Matrixformelberechnung nicht mitmacht und das WENN hier aber notwendig ist, um den #Wert-Fehler bei leeren Zellen zu vermeiden, da aufgrund der Textfuntionen die leeren Zellen nicht leer erscheinen, sondern als Leerstring, der nicht in eine Zahl wandelbar ist. Das WENN sorgt dann dafür, dass die Typumwandlung auch nur bei Zellen mit Inhalt durchgeführt wird und nicht bei leerzellen.
Gruß Daniel
Anzeige
schon richtig
18.08.2019 18:40:26
WF
nur, wenn in z.B. Zelle W27 #WERT! steht, ist die Formel im Eimer.
WF
AW: schon richtig
18.08.2019 18:56:31
Daniel
die Frage ist, warum da #WERT stehen sollte.
wenn das auch noch abgefangen werden muss.
die Kombination aus ProjektCode und Zeit dürfte ja eine manuelle Eingabe sein, wie soll da der Wert-Fehler auftreten?
und das jemand bei "Excel-Gut" in der Lage sein sollte, die Zellbereiche entsprechend anzupassen (was ja sowieso geschehen muss, da in der Fragestellung keine konkreten Zellbereiche vorgegeben wurden), davon sollte man eigentlich ausgehen können.
Gruß Daniel
nee, hab was gelernt
18.08.2019 19:14:11
WF
die Formel auf eine Zeile bezogen, ergibt 0 (null), wenn nicht als Arrayformel eingegeben.
Bei Bezug auf mehrere Zeilen kommt #WERT!
WF
Anzeige
Ach, mir fällt ein Stein ... - Vielen Dank
18.08.2019 19:21:56
Die
Hallo,
die Summe war es. Ich hatte heute Mittag schon mal die Matrix-Klammern erzeugt, war aber auch mit #WERT gescheitert. Mit Summe hat es gleich geklappt. Vielen Dank. Ja, die Bereiche habe ich angepasst und "ABC" war auch nicht der richtige Projektname ;-)
Noch eine kleine Frage: wenn ich nun mehrere Projekte erfragen möchte, kann ich dann so eine Art 'ODER' da einbauen? Also Projekt ABC oder DEF oder GHI. Oder besser die Formel 3x hintereinander?
Danke an Alle und eine schöne Arbeitswoche
wünscht Die Johanna
Wenn du mehrere Projekte zusammenfassen willst
18.08.2019 19:30:13
Daniel
dann so mit meiner Formel:
ersetze das Wenn(Links(A3:AD30;3)="ABC";...)
durch Wenn(IstZahl(Finden(Links(A3:AD30;3);"ABC;DEF;GHI"));...
wobei die Namen der zusammenzufassenden Projekte mit einem Trennzeichen als Text aufgelistet werden müssen.
Ich gehe hier mal davon aus, dass die Projektnamen alle gleich lang sind, ansonsten müsste man noch mit zusätzlichen Trennzeichem am Anfang und Ende für beide Parameter in FINDEN arbeiten.
Gruß Daniel
Anzeige
AW: Wenn du mehrere Projekte zusammenfassen willst
18.08.2019 19:48:18
Die
Hallo Daniel,
sorry, das klappt nicht. Ich habe - wie von dir oben geschrieben - die Hochkommata nur am Anfang und Ende der Projektnamen gesetzt ("ABC;DEF"). Da kommt das berühmte #WERT.
Mit Hochkommata nach jedem Text ("ABC";"DEF") kommt gar nichts, kein Fehler, aber auch kein Ergebnis.
Die [...sonst nehme ich die Vorgängerversion] Johanna
AW: Wenn du mehrere Projekte zusammenfassen willst
18.08.2019 19:55:22
Daniel
sorry, ich bin kein Hellseher.
ich kenne weder deine Formel, noch deine Datei in welcher du die Formel anwendest.
außderdem sieht das was du da gemacht hast noch nicht so aus, als hättest du meinen Vorschlag so umgesetzt, wie ich ihn beschrieben habe.
Ansonsten:
Hochkomma: '
Anführungszeichen: "
Wenn du schon keine Beispieldatei hochladen willst, dann sollstest wenigest genau ausführlich beschreiben, was du gemacht hast.
die bisherigen Erkenntnisse (Formel mit STRG+SHIFT+ENTER abschließen) sind natürlich weiterhin anzuwenden.
Gruß Daniel
Anzeige
AW: Wenn du mehrere Projekte zusammenfassen willst
18.08.2019 21:07:16
Die
Bitte entschuldige, wenn ich was falsches geschrieben habe.
Ich habe versucht, exakt deinen neuen Teil mit 'Ist-Zahl' in die Formel einzufügen. Natürlich weiterhin angepasst an meine Situation. Die Formel sieht jetzt so aus:
{=SUMME(($DH$7:$EZ$7=$AII$7)*WENN(ISTZAHL(FINDEN(LINKS($DH20:$EZ20;3);"ABC;DEF"));TEIL($DH20:$EZ20;4; 3);0)) }
Natürlich mit STRG+SHIFT+ENTER abgeschlossen. In DH steht die KW des jeweiligen Tages, in AII die aktuelle KW. Als Ergebnis steht "#WERT". Zuvor habe ich mit den gleichen Daten den korrekten Wert für ABC ermitteln können.
Ich bin so froh, dass du mir geholfen hast. Ich wollte keineswegs dich kritisieren, sondern nur beschreiben, was passiert.
'Asche auf mein Haupt' von
Der Johanna
Anzeige
AW: Wenn du mehrere Projekte zusammenfassen willst
18.08.2019 21:22:21
Daniel
oK; das Problem ist, dass jetzt die Leerzellen nicht mehr ausgeschlossen sind, weil der text "" überall gefunden werden kann.
ich würde das Problem hier so lösen, indem ich einfach noch einen Dummy-Text, der nicht als Projektbeschriebung verwendet wird, anhänge, so dass es auch für leerzellen einen Text gibt, der gesucht wird. Alternativ könnten man auch eine weitere WENN-Abfrage einbauen, aber das wäre aufwendiger.
LINKS($DH20:$EZ20&"---";3)
gruß Daniel
AW: Wenn du mehrere Projekte zusammenfassen willst
18.08.2019 21:22:22
Daniel
oK; das Problem ist, dass jetzt die Leerzellen nicht mehr ausgeschlossen sind, weil der text "" überall gefunden werden kann.
ich würde das Problem hier so lösen, indem ich einfach noch einen Dummy-Text, der nicht als Projektbeschriebung verwendet wird, anhänge, so dass es auch für leerzellen einen Text gibt, der gesucht wird. Alternativ könnten man auch eine weitere WENN-Abfrage einbauen, aber das wäre aufwendiger.
LINKS($DH20:$EZ20&"---";3)
gruß Daniel
Anzeige
AW: Wenn du mehrere Projekte zusammenfassen willst
19.08.2019 07:43:10
Die
Hallo Daniel,
gestern Abend habe ich es nicht mehr geschafft, aber jetzt habe ich die Formel ergänzt - DANKE. Das Ergebnis steht da und ist richtig. Das hätte ich selbst nicht geschafft - danke für deine Mühe.
Danke an alle und eine schöne Woche
freut sich
Die Johanna

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige