Summenprodukt mit Datum, Text und absolutem Bezug

[FaChJu]

Hi Excel-Freunde, 

ich werde bald verrückt. Bin zwar kein Excel-Neuling mehr, aber hier komme ich an meine Grenzen ohne Makro (was ich gern vermeiden möchte - was sicher auch geht, aber meine Kreativität fehlt).

Bitte entnehmt meine Vorstellung der angehängten Excel-Datei. Die Formatierung muss aufgrund globaler Verwendung unbedingt so bleiben.


Ich möchte wie folgt auswerten:
E2 = wieviele Anfragen habe ich im Juni 2017 für Kopf erhalten
F2 = wie hoch ist der Potentielle Umsatz für Kopf im Juni 2017


Meine Teillösung sieht wie folgt aus: 
E2 = SUMMENPRODUKT((MONAT($A$2:$A$6)=6)*(JAHR($A$2:$A$6)=2017))

Jetzt bekomme ich schonmal die Antwort auf "wieviele Anfragen habe ich im Juni 2017 bekommen".

Folgende Probleme: 
1) Ich habe noch nicht die Bedingung "Kopf" abgefragt
2) wenn ich jetzt eine Zeile Oberhalb A2 einfüge (hätte ich gern, damit die letzte Anfrage immer oben steht) verschiebt sich mein absoluter Bezug nach unten, also auf $A$3:$A$7
3) jetzt habe ich die potenziellen Umsätze noch nicht hinzugelesen in z.B. F2 -> SUMMEWENNS?? 

Problem 2) habe ich umgangen indem ich die Überschrift mit eingebunden habe, als $A$1:$A$6. Das funktioniert für alle anderen (sind zig in der Datei) Formeln hervorragend, aber eben nicht für das Summenprodukt. 


Fragen die euch helfen mein Problem zu lösen, immer her. Mir fällt echt nichts mehr ein. 

Könnt ihr mir helfen?

[lupo1]

E2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
F2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf");B$2:B$6)

oder für Excel seit 2007 auch:

E2: =SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))

[shift-del]

Hallo

Ich würde es keinesfalls mit einer Pivot-Tabelle auswerten.
Auf jeden Fall nicht "Datum der Anfrage" in den Zeilenbereich ziehen und nach Jahre und Monate gruppieren.
Auf jeden Fall nicht "Kategorie" Spaltenbereich ziehen.
Auf jeden Fall nicht "Potentieller Umsatz" und "Kategorie" in den Wertebereich ziehen.
Und keinesfalls "Σ Werte" in den Zeilenbereich ziehen.

[FaChJu]

E2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
F2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf");B$2:B$6)

oder für Excel seit 2007 auch:

E2: =SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))

hier auch nochmal vielen Dank! 

magst du mir folgendes erklären? Also wofür {-1.1} oder &{6.7}
E2: =SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))

[lupo1]

Gib mal

=6&-17

und dann

=--(6&-17)

selbst ein. Danach formatierst Du die 42887 als Datum.

Mit {-1.1} ziehe ich - nach dem obigen Strickmuster - ALLE Daten vor Juni von ALLEN vor Juli 2017 ab. Was resultiert daraus? Der Juni.

Vorteil der zweiten Schreibweise:

F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{4.7}&-17;C$2:C$6;"Kopf")) ergibt April bis Juni

F2: =SUMME({-1.1.-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{1.3.5.7}&-17;C$2:C$6;"Kopf")) ergibt Jan/Feb/Mai/Jun

[FaChJu]

E2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
F2: =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf");B$2:B$6)

oder für Excel seit 2007 auch:

E2: =SUMME({-1.1}*ZÄHLENWENNS(A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))
F2: =SUMME({-1.1}*SUMMEWENNS(B$2:B$6;A$2:A$6;"<"&{6.7}&-2017;C$2:C$6;"Kopf"))

zu früh gefreut... habe mich für folgende Formel entschieden =SUMMENPRODUKT((TEXT(A$2:A$6;"JMM")="1706")*(C$2:C$6="Kopf"))
funktioniert hervorragend, ABER jetzt bleibt noch mein ursprüngliches Problem:
wenn ich oberhalb $A$2:... eine Zeile einfüge ändert sich der Bezug auf $A$3:...
Excel müsste bei $A$1:... beginnen. dann aber wirft Excel mir #NV aus.

Lösung möglich?

[lupo1]

A$2:A$6 ersetzst Du durch INDEX(A:A;2):A$6 und die anderen entspr.

Mit

F2: =SUMMENPRODUKT(--(TEXT(A$1:A$6;"JMM")="1706");--(C$1:C$6="Kopf");B$1:B$6)

ginge es aber auch (Doppelminus und Semikola). Ist sogar 1,3mal schneller, als mit *.

[Rabe]

Hi FaChJu,

nimm, wie shift-del geschrieben hat, eine Pivot-Tabelle, dann kannst Du ganz ohne Formeln alles total einfach auswerten.


Tabelle1

	A	B	C	D	E	F	G	H	I	J
1	Datum der Anfrage	Potenzieller Umsatz	Kategorie
2	12-Jun-17	10000	Kopf		3
3	19-Jun-17	20000	Hals
4	26-Jun-17	30000	Brust				Zeilenbeschriftungen	Summe von Potenzieller Umsatz	Anzahl von Kategorie
5	3-Jul-17	40000	Hand				'2017
6	10-Jul-17	50000	Fuß				Jun	60000	3
7	11-Jul-17	50001	Fuß				Brust	30000	1
8							Hals	20000	1
9							Kopf	10000	1
10							Jul	140001	3
11							Fuß	100001	2
12							Hand	40000	1
13							Gesamtergebnis	200001	6
14
15

verwendete Formeln
Zelle	Formel	Bereich	N/A
E2	=SUMMENPRODUKT((MONAT($A$2:$A$6)=6)*(JAHR($A$2:$A$6)=2017))

Excel-Inn.de

Hajo-Excel.de

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

Add-In-Version 19.08 einschl. 64 Bit

Wenn Du dann noch vorher die belegten Bereiche Spalte A bis C in eine intelligente Tabelle umwandelst (mit STRG-L), passt sich der Pivot-Datenbereich automatisch an.

[shift-del]

nimm, wie shift-del geschrieben hat, eine Pivot-Tabelle, dann kannst Du ganz ohne Formeln alles total einfach auswerten.

Nein, nein, nein. Ich habe klar geschrieben dass keine Pivot-Tabelle verwendet soll.
Wenn es möglich wäre dann würden Admins diese Funktion sofort abschalten. Viel zu effizient.

[Rabe]

Hi Detlef,

Nein, nein, nein. Ich habe klar geschrieben dass keine Pivot-Tabelle verwendet soll.
Wenn es möglich wäre dann würden Admins diese Funktion sofort abschalten. Viel zu effizient.

nu sei doch nicht so ironisch. Das versteht nicht jeder.
:19: :21: