Wert zählen wenn Datum in selber zeile in bestimmtem Bereich?
Hallo,
Ich möchte eine Exceltabelle führen, in der ich meine Arbeitsstunden für die nächsten Paar monate erfasse. Dazu habe ich eine Tabelle angelegt (siehe bild). Ich würde im rechten bereich gerne pro monat erfassen wieviele stunden ich schon gearbeitet habe. Das heißt also in den fragezeichen feldern müsste dann soetwas stehen wie =ZÄHLENWENN wobei der zählbereich die gesamte Spalte mit den Stunden (rechts neben "Thema") wäre. Die bedingung die dann formuliert werden müsste prüfen ob das datum ganz links im richtigen bereich ist.
Wie mache ich das?
2 Antworten
Zählenwenn liefert Dir die Anzahl, Du willst aber ja eine Summe.
Das geht z.B. auf die Art, wenn Deine Tätigkeitsliste sich nur auf ein Jahr bezieht, dann reicht der Vergleich des Monats aus.
=SUMMENPRODUKT((MONAT(A6:A11)=MONAT(9))*(C6:C11))
Statt direkt die 9 für September in die Formel zu schreiben, kann man mit der richtigen Formatierung auch die Zelle in F6 (die Monatsnamen) einbeziehen. Dazu muss in F6 etc. aber statt dem Wort ein richtiges Datum stehen, also statt September 01.09.2016. Zellformatierung auf benutzerdefiniert und dort MMMM
dann wird auch September angezeigt, der Inhalt ist aber ein richtiges Datum, mit dem man rechnen kann.
Um die Formel für die anderen Monate kopieren zu können, ohne sie anpassen zu müssen, noch die Zeilen fixieren:
=SUMMENPRODUKT((MONAT(A$6:A$11)=MONAT(F6))*(C$6:C$11))
bei Du den Bereich so erweitern solltest, dass die gesamte Liste inkl. künftiger Einträge abgedeckt ist.
Alternativ, da wäre der Vergleich mit dem Jahr auch enthalten:
=SUMMEWENNS(C6:C11;A6:A11;">="&F6;A6:A11;"<"&EDATUM(F6;1))
Hey Ninombre,
könntest du mir die Formel mal bitte erklären, die ist echt genial, jedoch versteh ich die Syntax nich so ganz und wie das Teil arbeitet...
Würde das gerne verstehen =D
Grüße,
Philip
summenprodukt kann in der Tat recht häufig sinnvoll angewendet werden. Im Grunde wird (meist zeilenweise) eine Reihe von Multiplikationen summiert. Im konkreten Fall Spalte A * Spalte C, also A6*C6+A7*C7 etc.
Wenn man Bedingungen hinterlegt (hier monat(a6:a11)=9), wird zeilenweise WAHR und FALSCH ermittelt. WAHR = 1 und FALSCH = 0. Ohne Bedingung würde der Zellinhalt in der Multiplikation verwendet (im Beispiel also Spalte C mit dem Stundenwert)
Es werden also in Spalte A abhängig vom Datum jeweils 0 oder 1 ermittelt und mit der Stundenzahl in Spalte C multipliziert. Bei passendem Datum wird die Stundenzahl in C aufsummiert (da jedes gültige Datum eine 1 liefert und ein ungültiges als 0 dazuführt, dass die Stundenzahl beim Datum außerhalb des Monats nicht aufsummiert wird)
Dadurch, dass Bedingungen als 0 und 1 interpretiert werden und man Bereiche in Funktionen hinterlegen kann (hier monat, ginge auch mit Länge oder ähnlichem) kann man mit summenprodukt flexibler arbeiten und summewenn oder zählenwenn ersetzen.
Wenn Du bspw. nur die Anzahl der Datumswerte im August ermitteln wolltest kann man
=summenprodukt((monat(a6:a11)=8)*1) verwenden. Die 1 ist notwendig, damit es was zu multiplizieren gibt...
DH! hätte auch Summenprodukt vorgeschlagen, auch deshalb, weil die Syntax von SummewennS anders ist als die von Summewenn (bzw Zählenwenn).
ich hätte allerdings von vornherein September als 1.9.2016 im Format MMMM vorgeschlagen (was du ja auch hast), und dann als Monatszeitraum zB
Summenprodukt(C6*(A:A>=F6)*(A:A<=Monatsende(F6))
genommen (wobei die genannten Datümer aber im August liegen, das Summenprodukt also 0 ergibt). Feinheiten und ungeprüft, evtl die $-Zeichen noch anders gesetzt, , aber im Wesentlichen genauso.
Legt neben das Datum eine Hilfsspalte an, wo du aus dem Datum das Monat rausbekommst(z.b. mit der Funktion "=Monat()" ) und dann einfach =Zählenwenn () und als Suchkriterium z.b. 10 für Oktober:D
Danke für deine Antwort!
Ich habe das probiert, aber leider erhalte ich wenn ich die Formel einfüge als Summe 0:
http://venion.net/files/stundenplan_2.png