Frage von TomMetal, 46

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?

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Ninombre, Community-Experte für Excel, 38

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.


Kommentar von Ninombre ,

Alternativ, da wäre der Vergleich mit dem Jahr auch enthalten:

=SUMMEWENNS(C6:C11;A6:A11;">="&F6;A6:A11;"<"&EDATUM(F6;1))
Kommentar von TomMetal ,

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

Kommentar von Ninombre ,

ist mir beim Überarbeiten der Formeln reingerutscht:
Wenn Du den Monat direkt in die Formel einträgst, dann nur die Zahl, also

=SUMMENPRODUKT((MONAT(A6:A11)=9)*(C6:C11))

Kommentar von iMPerFekTioN ,

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

Kommentar von Ninombre ,

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...

Kommentar von Iamiam ,

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.

Antwort
von Lycaner1985, 30

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

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten