Office: Berechnen mit Summenprodukt

Hallo Excel Freaks

Ich stütze mich auf meine Frage die ich schon einmal gestellt hatte. (Danke René für deine Hilfe!)
http://www.office-hilfe.com/support/showthread.php/16533-Summenprodukt-mit-Wildcard

Wie ihr seht, kann ich den Umsatz nicht berechnen. Was mache ich falsch? Liegt es am dynamischen Namen "Extrakosten"?
Die Liste kann erweitert werden, deshalb die dynamischen Namen. Oder gibt es eine andere Möglichkeit wie man den Umsatz berechnen kann?

Danke für eure Hilfe

Maria

 

Hallo, ja, es liegt am definierten Namen. Dadurch, dass in Spalte C in einigen Zellen keine Werte eingetragen werden stimmen die Bereiche nicht mehr überein (Anzahl2) und darauf reagiert Summenprodukt allergsich. Du kannst das Problem ganz einfach umgehen indem du in Spalte C in den leere Zellen einfach eine 0 eingibst.

 

Hallo Peter

Danke für die Info. Gibt es eine Möglichkeit dass Excel alle leere Zellen als 0 behandelt?
Oder ein Zusatz die ich in die Formel einfügen kann?

Maria

 

Hallo, du könntest die Namensdefinition für Extrakosten ändern. Unter der Voraussetzung, dass in Spalte A immer ein Eintrag erfolgt könntest du es mal mit dieser Formel probieren: =BEREICH.VERSCHIEBEN(Geschäfte!$C$2;;;ANZAHL2(Geschäfte!$A:$A)-1;1)

 

Hallo Peter

Ich habe es versucht aber sobald ich eine neue Zeile einfüge funktioniert es nicht :-(

Liebe Grüsse
Maria

 

Hallo,

das kann ich so nicht nachvollziehen.

Wenn ich die Namensdefinition für Extraskosten wie beschrieben ändere dann passiert bei mir folgendes:

Wenn in Spalte A ein Eintrag erfolgt erscheint in der Spalte G die Fehlermeldung #Wert und ab Spalte H die Fehlermeldung #NV. Aber wenn dann ein Datum in Spalte B eingegeben wird werden in den Spalten korrekte Werte angezeigt.

 

hi, sehr interessant sind auch konstrukte wie (MONAT(0&Auftragsdatum) oder JAHR(0&Auftragsdatum) was bewirkt denn hier 0&... - ich will das nicht wirklich wissen... ... einfach weglassen...

ich würde auch bei der namensvergabe auf BEREICH.VERSCHIEBEN() verzichten... INDEX() geht genauso gut...

 

Hallo,

da kann ich Jörg nur recht geben, mit INDEX wäre es besser. Zudem sollte man alle Formeln einheitlich gestalten.

[TABLE]
<colgroup><col style="width: 28ptpx"><col width="68,25pt"><col width="36,75pt"><col width="74,25pt"><col width="73,5pt"><col width="63pt"><col width="52,5pt"><col width="64,5pt"><col width="49,5pt"><col width="63pt"></colgroup><tbody>[TR="bgcolor: #FAFAFA"]
[TD="colspan: 10, align: center"]Arbeitsblatt mit dem Namen 'Geschäfte'[/TD]
[/TR]
[TR="bgcolor: #cacaca"]
[TD] [/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"] [/TD]
[TD="bgcolor: #EEECE1, align: center"]Anzahl[/TD]
[TD="bgcolor: #EEECE1, align: center"]Umsatz[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Migros[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Umsatz[/TD]
[TD="bgcolor: #EBF1DE, align: center"]Denner[/TD]
[TD="bgcolor: #EBF1DE, align: center"]Umsatz[/TD]
[TD="bgcolor: #DAEEF3, align: center"]Coop[/TD]
[TD="bgcolor: #DAEEF3, align: center"]Umsatz[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Januar[/TD]
[TD="bgcolor: #EEECE1, align: center"]1[/TD]
[TD="bgcolor: #EEECE1, align: center"]Fr. 5,00[/TD]
[TD="bgcolor: #DCE6F1, align: center"]1[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Fr. 5,00[/TD]
[TD="bgcolor: #EBF1DE, align: center"]0[/TD]
[TD="bgcolor: #EBF1DE, align: center"]Fr. -[/TD]
[TD="bgcolor: #DAEEF3, align: center"]0[/TD]
[TD="bgcolor: #DAEEF3, align: center"]Fr. -[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]Februar[/TD]
[TD="bgcolor: #EEECE1, align: center"]2[/TD]
[TD="bgcolor: #EEECE1, align: center"]Fr. 6,00[/TD]
[TD="bgcolor: #DCE6F1, align: center"]2[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Fr. 6,00[/TD]
[TD="bgcolor: #EBF1DE, align: center"]0[/TD]
[TD="bgcolor: #EBF1DE, align: center"]Fr. -[/TD]
[TD="bgcolor: #DAEEF3, align: center"]0[/TD]
[TD="bgcolor: #DAEEF3, align: center"]Fr. -[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]März[/TD]
[TD="bgcolor: #EEECE1, align: center"]2[/TD]
[TD="bgcolor: #EEECE1, align: center"]Fr. 6,00[/TD]
[TD="bgcolor: #DCE6F1, align: center"]2[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Fr. 6,00[/TD]
[TD="bgcolor: #EBF1DE, align: center"]0[/TD]
[TD="bgcolor: #EBF1DE, align: center"]Fr. -[/TD]
[TD="bgcolor: #DAEEF3, align: center"]0[/TD]
[TD="bgcolor: #DAEEF3, align: center"]Fr. -[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR="bgcolor: #eeaaee"]
[TD]Name[/TD]
[TD]Bezug[/TD]
[/TR]
[TR]
[TD]Auftragsdatum[/TD]
[TD]=Geschäfte!$B$2:INDEX(Geschäfte!$B:$B;VERWEIS(9;1/(Geschäfte!$A$1:$A$1000<>"");ZEILE(Geschäfte!$A$1:$A$1000)))[/TD]
[/TR]
[TR]
[TD]Extrakosten[/TD]
[TD]=Geschäfte!$C$2:INDEX(Geschäfte!$C:$C;VERWEIS(9;1/(Geschäfte!$A$1:$A$1000<>"");ZEILE(Geschäfte!$A$1:$A$1000)))[/TD]
[/TR]
[TR]
[TD]Geschäft[/TD]
[TD]=Geschäfte!$A$2:INDEX(Geschäfte!$A:$A;VERWEIS(9;1/(Geschäfte!$A$1:$A$1000<>"");ZEILE(Geschäfte!$A$1:$A$1000)))[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>[TR="bgcolor: #eeaaaa"]
[TD]Zelle[/TD]
[TD]Formel[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011))[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011);Extrakosten)[/TD]
[/TR]
[TR]
[TD]H2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011)*ISTZAHL(SUCHEN($H$1;Geschäft)))[/TD]
[/TR]
[TR]
[TD]I2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011)*ISTZAHL(SUCHEN($H$1;Geschäft));Extrakosten)[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011)*(Geschäft=$J$1))[/TD]
[/TR]
[TR]
[TD]K2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011)*(Geschäft=J$1);Extrakosten)[/TD]
[/TR]
[TR]
[TD]L2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011)*(Geschäft=$L$1))[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD]=SUMMENPRODUKT((MONAT(Auftragsdatum)<=MONAT(E2&1))*(JAHR(Auftragsdatum)=2011)*(Geschäft=L$1);Extrakosten)[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE]
<tbody>[TR]
[TD]Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg[/TD]
[/TR]
</tbody>[/TABLE]



Gruß

Edgar