Office: Frage zu SUMMEWENNS + JAHR

Hallo zusammen

Wiedermal eine (dämliche) Frage von mir... siehe angehängte Grafik: was muss ich im Feld D5 in der Formel

=SUMMEWENNS(B:B;A:A;D3;C:C;D4)

ändern, damit das Jahr aus A:A erkannt wird und das Ergebnis "12" angezeigt wird?





Danke für eure Hilfe!

:)

 

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.
aussagekräftigen Dateinamen hoch oder benutze ein Tool zur Tabellenanzeige.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Test, Mappe, Beispiel usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten

 

Danke für den Hinweis. Hab nun eine Demomappe hochgeladen.

 

es ist die falsche Datei. In D5 steht keine Formel.

Tabelle1

	D
5	12

verwendete Formeln
Zelle	Formel	Bereich	N/A
D5	=SUMMENPRODUKT((JAHR(A1:A7)=2017)*(B1:B7)*(C1:C7="C"))

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 24.04 einschl. 64 Bit

 

Es funktioniert nicht, wenn du im Kriterium "2017" angibst und in der Spalte A das komplette Datum hinterlegt ist.

In deiner Formel " =SUMMEWENNS(B:B;A:A;D3;C:C;D4)" steht das
"A:A" für den Bereich, der mit "D3" verglichen werden soll. Trifft das zu, wird der Wert aus der Spalte B aufaddiert.
Da aber "2017" nicht gleich 05.08.2017 ist, trifft das Kriterium nie zu. Deswegen ergibt sich immer eine Null.

Du kannst eine Hilfspalte einfügen, die aus dem Datum der Spalte A mit der Funktion JAHR das Jahr generiert. (=Jahr(A1) )
Dann kannst du die Hilfsspalte ausblenden.
In deiner Formel kannst du dann das "A:A" durch die Hilfspalte ersetzen.

Ich habe eine Spalte B eingefügt. Daraus ergibt sich " =SUMMEWENNS(C1:C7;B1:B7;E3;D1:D7;E4) "

Dann klappt es auch.

 

Hallo,

ohne Hilfsspalte:
Code:

beachte: die Bereiche müssen alle gleichgroß sein, ganze Spalten führen u.U. zu langer Rechenzeit, da Matrizenmultiplikation rechenintensiv sind.

Code:

das "--" bewirkt das wandeln von wahr/falsch nach 1/0

Markiere einen kleinen Teil der Formel (bspw. --JAHR($A$1:$A$7)), denn drücke F9, da erhälst du das Zwischenergebnis. So kannst du die Formeln prima nachvollziehen.

edit: ich habe es auch mit summenwenns und jahr() probiert. Evtl. bekommt das jemand erklärt, warum das nicht funktioniert.

 

hallo Ralf, da hat sich aber einer in das minus-zeichen verguckt... - genau diese braucht's hier aber überhaupt nicht... (^_-) UND: es geht auch ohne formel mit PIVOT

Arbeitsblatt mit dem Namen 'Tabelle1'
*	A	B	C	D	E	F	G
1	Datum	Wert	Kriterium	*	*	*	*
2	01.01.2016	5	a	*	*	*	*
3	01.02.2016	5	a	*	Kriterium	c	*
4	01.03.2016	5	a	*	*	*	*
5	02.04.2016	5	b	*	Summe von Wert	*	*
6	01.03.2017	6	b	*	Jahre	Datum	Ergebnis
7	01.06.2017	6	c	*	2017	Jun	6
8	05.08.2017	6	c	*	*	Aug	6
9	*	*	*	*	Gesamtergebnis	*	12

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

 

Hallo Frank,

ja, ich weiß, dass die Minus nicht nötig sind. Es lässt sich einfacher mit der Matrixmultiplikation erklären (ist für viele ja ein rotes Tuch). Und die Fehlersuche ist einfacher wegen der 8192 Zeichenbegrenzung bei F9.
Des Weiteren nutze ich die Schreibweise auch beim VERWEIS(2;1/--(...)/--(...);(...)).

Zum Pivot: das Kriterium hast du in den Zeilenbereich oder in den Filter gezogen? Ich hatte auch erst an Pivot gedacht, dachte jedoch, dass der TE mit dem Wert weiterrechnen mag, anstatt eine Auswertung zu haben.

 

Hallo zusammen

Vielen Dank euch allen, ihr habt mir super geholfen! Mit Summenprodukt habe ich genau gefunden, wonach ich gesucht habe. Mit Hilfsspalten und Pivot würde man auch zum Ziel gelangen, aber Summenprodukt ist etwas eleganter. *Smilie

Danke & schönes Wochenende!

 

hallo RalfP., das mit dem "--" und dem "*" ist aber doppeltgemoppelt... *cool.gif*

 

ja, sagte ich. Aber:
YEAR($A$1:$A$7)=$D$3 = {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

oder:
(--(YEAR($A$1:$A$7)=$D$3)) = {0;0;0;0;1;1;1}

ist einfacher zu lesen und kontrollieren für mich:
=SUMPRODUCT({0;0;0;0;1;1;1}*{0;0;0;0;0;1;1}*{5;5;5;5;6;6;6})
daran ist sofort das Ergebnis 12 abzulesen.