Clever-Excel-Forum

Hallo Excel-Gemeinde, 

ich hoffe jemand von Euch kann mir helfen. Im Rahmen meiner Bachelorarbeit möchte ich eine Aussage über den Innovationsgrad des Produktportfolios treffen können.

Die Innovationsartikel definieren sich über das SOP (Start of Production). Die Artikel gelten ab dem SOP + 2 Jahre als Innovation. 

Als Berechnungsgrundlage würde ich daher gerne die Summe des Gesamtumsatzes in ein Verhältnis zu dem Umsatz der Innovationsartikel setzen. 

In der Excel im Anhang habe ich über die Summewenn Funktion eine vereinfachte Lösung hinbekommen, in dem ich das SOP Datum auf eine ganze Jahreszahl abgerundet habe. 

In der Praxis ist es jedoch so, dass ein exaktes Datum ausgelesen wird. 

Außerdem funktioniert meine gebaute Formel nur für das laufende Jahr. (Nicht SOP + 2 Jahre) 

Meine Formel: =SUMMEWENN($D$5:$D$31;F$4;F$5:F$31)

Im Anhang habe ich eine anonymisierte Testdatei, welche repräsentativ für meine Tabelle stehen soll. (Datenschutz etc...)

Für mehrere Jahre bin ich in der Annahme, dass ich die Summewenns Funktion benötige, bin mir jedoch nicht sicher. 

Hatte die Formel schon gebaut, leider hat diese dann teilweise die Werte doppelt bzw. dreifach ausgegeben, je nach Übereinstimmung. (weil SOP + 2 Jahre)

Ich hoffe ich konnte mich einigermaßen verständlich ausdrücken. 

Vielen Dank vorab! 

Liebe Grüße

Hola,

das geht wahrscheinlich mit Summenprodukt.
In welchen Zellen erwartest du denn genau welches Ergebnis? Ein Wunschergebnis kann ich nicht erkennen.

Gruß,
steve1da

Hallo T...,

da in den Umsatzspalten nicht ausgewiesen wird an welchem Tag des Jahres der Umsatz anfiel, kann man das nur Jahresweise ermitteln.
Für F35:
=SUMMEWENNS(F$5:F$31;$D$5:$D$31;">="&(F4-1);$D$5:$D$31;"<="&F4)

Hi,

so? Formel in F39 eintragen und nach rechts ziehen.

Arbeitsblatt mit dem Namen 'Tabelle1'
	B	C	D	E	F	G	H	I	J	K	L
37	keine Formel	manuell die richtigen Daten summiert	"+ 2 Jahre"	Innovation (Umsatz)			9.000,00 €	9.000,00 €	18.000,00 €	9.000,00 €	9.000,00 €
38
39	Formel	Summewenns	"+ 2 Jahre"	Innovation (Umsatz)	0	0	9000	9000	18000	9000	9000

Zelle	Formel
F39	=SUMMEWENN($D$5:$D$31;D$4;D$5:D$31)+SUMMEWENN($D$5:$D$31;E$4;E$5:E$31)+SUMMEWENN($D$5:$D$31;F$4;F$5:F$31)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

Hallo Steve, 

vielen Dank für die schnelle Antwort. 

Ich erwarte, dass in den Zellen F39-N39 die Summe der Umsätze für die Innovationsprodukte berechnet wird. (jeweiliges Jahr)

In den Zellen F37-N39 habe ich dieses Vorgang durch manuelles Summieren simuliert, um später eine Gegenprobe für die Formel zu haben.

Die Formel muss also prüfen, ob in Spalte E das Datum mit dem laufenden Jahr übereinstimmt, bzw. ob das laufende Jahr maximal 2 Jahre älter als der SOP ist.

Sprich ein Artikel aus dem Jahr 2019 gilt in den Jahren 2020 und 2021 immer noch als Innovation und muss somit in den Umsatz der Innovationen berechnet werden.

Ich hoffe du hast mein Problem verstanden. 

Danke dir!

Grüße

---

Hallo WillWissen, 

vielen Dank, das ist die Lösung wenn ich auf Spalte D zurückgreifen kann. 

Geht das auch, wenn ich diese nicht zur Verfügung habe?

Diese habe ich nämlich nach auslesen der Daten manuell hinzugefügt. 

Grüße

Hi,

ich würde die Spalte als Hilfsspalte drin lassen. Du kannst die Jahreszahlen mit 
aus deiner Datumsspalte extrahieren.

Die Hilfsspalte blendest halt bei Bedarf einfach aus.

Hallo zusammen, 

ich dachte ursprünglich das wäre die Lösung, da das Ergebnis richtig war. 

Als ich die Formel in der Originaldatei probiert habe, viel schnell auf, dass die Summen nicht stimmen können. 

Vermutlich war die Testdatei blöd, da hier jeder Artikel 1.000€ Umsatz hat. Deshalb ist der "Fehler" nicht aufgefallen. 

Ich habe die Testdatei nun etwas manipuliert, hier sieht man in der Probe, dass das Ergebnis nicht stimmt. 

Vielen Dank vorab!  :22:

Hallo T...,

vom 1.1.2019 oder 1.3.2019 bis Ende 2021 sind aber mehr als zwei Jahre.

Wenn du in meiner Formel "-1" durch "-2" ersetzt, bekommst du dein gewünschtes Ergebnis.

Hallo Ego, 

danke dir!  :05:

Grüße