Summenprodukt oder ZählenWenn?

Question

Hallo Helfer,

www.file-upload.net/download-2149890/Monats-Summen.xls.html
eine Musterdatei die mein Problem sichtbar macht:
In tab-Blatt "EK-Bons" sind Kassenbons verschiedener Firmen in unregelmässiger Reihenfolge (Datum), mit unterschiedlichen Kategorien versehen, eingetragen.
In tab-Blatt "Kaufdatum" sollen Ergebnisse wie hier "per Hand" sinnvoll so eingetragen werden, daß Summen mindestens nach Monaten und Kategorien (nach Möglichkeit auch noch nach Händlern getrennt) aufbereitet werden.
Es soll in Excel möglich sein und ohne VBA. Selbst habe ich schon mit "ZählenWenn" bei der Kategorie versucht zumindest hier auf einen grünen Zweig zu kommen, aber ich bekomme immer Fehlermeldungen wie "Wert" oder "#NV" usw.
Wäre sehr froh über eine Hilfestellung was die Formeln betrifft.
Auch die Verbindung mit den einzelnen Monaten ist (für mich) sehr schwierig.

Danke an alle Helfer, die bei dem Mistwetter vor dem PC sitzen und sich die Mühe machen meine Probe-Datei mal durchzuchecken.

Gruß Inge

[*]
[sup]*Threadedit* 11.01.2010, 17:15:52
Admininfo: Führ bitte Threads nicht fort indem du Weitere eröffnest, und vermeide Mehrfachanfragen. Die Datenbank und User werden es dir danken. Siehe FAQ 2, #3.[/sup]

Answer 1

Hallo - die Zweite
keine Ahnung, warum der Link nicht geht. Von meinem Woprd aus geht er. Hier nochmal ne Kopie
DownLink:
http://www.file-upload.net/download-2149890/Monats-Summen.xls.html

Gruß Inge

Answer 2

Halo Inge,

Du meinst die Formel in Spalte B?
=SUMMENPRODUKT(('EK-Bons'!$H$2:$H$19=C2)*(MONAT('EK-Bons'!$A$2:$A$19)=MONAT(A2))*(JAHR('EK-Bons'!$A$2:$A$19)=JAHR(A2))*'EK-Bons'!$F$2:$F$19)

Gruß Hajo

Answer 3

Hallo Inge,

ich gehe mal von folgenden Spalten in Blatt "EK-Bon" aus: SpalteA = lfd. Nr., SpalteB = Datum, SpalteC = Artikel, SpalteD = Stück, SpalteE = Kategorie, SpalteF = Händler, SpalteG = Einzelpreis, SpalteH = Ges.-Preis.

Als Beispiel nutze ich jetzt nur die Spalten SpalteB (Datum), SpalteE (Kategorie), SpalteF (Händler) und natürlich SpalteH (Ges.-Preis).

Um die Formel etwas kürzer und damit übersichtlicher zu machen, berechne ich die erste Summe erstmal in demselben Blatt "EK-Bon", z. B. in K1, für Dezember 2009, Kategorie = "Obst" und Händler = "Kaufhof":

=SUMMENPRODUKT((B3:B33 >= DATWERT("01.12.2009")) * (B3:B33 < DATWERT("01.01.2010")) * (E3:E33 = "Obst") * (F3:F33 = "Kaufhof") * (H3:H33))

Und nun muss man die Details anpassen (siehe Fortsetzung).

MfG Charlotte

Answer 4

@Hajo,
große Klasse!
Dadurch Hast du mir sehr viel "Handarbeit" erspart.
Mit der Datums- und Kategorienspalte in Verbindung der Summen klappt das jetzt wunderbar.
Wäre es noch möglich, diese Daten (aus Spalte "B-Kaufdatum") nach dem Ergebnis deiner Formel in einer weiteren Spalte nochmals nach den Händlern (Spalte "B-EK-Bons") zu unterteilen?
Falls nicht, ists auch nicht so schlimm :-)
Ich werd jetzt esrt mal versuchen, die Syntax deiner Formel zu ergründen.

Vielen Dank nochmal
Gruß Inge

Answer 5

Halo Inge,

Du mußt nur eine Spalte haben wo der Händler steht und dann nur ein zusätzliches Produkt einfüen
*('EK-Bons'!$B$2:$B$19=I2)

Gruß Hjao

Answer 6

@Charlotte,
die Antwort an Hajo hat sich jetzt mit deinem Posting überschnitten. Der Lösungsansatz sieht auch interessant aus. Ich werde mal meine Tabelle etwas umstellen (habe bisher keine laufende Nr. in Spalte "A". Frage dazu: von Vorteil oder nicht wichtig?) und bin gespannt auf deine Fortsetzung.

liebe Grüße und Danke auch dir
Inge

Answer 7

Hallo Inge,

hier nun die Fortsetzung (da eine Antwort durch Unterbrechungen bei mir manchmal erst sehr spät fertig wird, habe ich sie diesmal in mehrere Teile geteilt).

Setze ich das Beispiel fort und nehme an, dass man z. B. die Kategorien und die Händler besser nicht fest in die Formeln eingibt, sondern in Zellen schreibt (Kategorien in Spalte I, Händler in Spalte J) und mit Bezügen darauf zugreift, dann könnte man folgende Erweiterungen vornehmen:

I1: Obst
I2: Gemüse
I3: Milchprodukte

J1: Kaufhof
J2: ALDI
J3: REWE

Spalte L - Übersicht nach Kategorien:
L1: =I1
=SUMMENPRODUKT((B3:B33 >= DATWERT("01.12.2009")) * (B3:B33 < DATWERT("01.01.2010")) * (E3:E33 =$L$1) * (F3:F33 = J1) * (H3:H33))
runterziehen z. B. bis L19

L21: =I2
=SUMMENPRODUKT((B3:B33 >= DATWERT("01.12.2009")) * (B3:B33 < DATWERT("01.01.2010")) * (E3:E33 =$L$21) * (F3:F33 = J1) * (H3:H33))
runterziehen z. B. bis L39 usw.

Spalte M - Übersicht nach Händlern:
M1: =J1
M2: =SUMMENPRODUKT((B4:B34 >= DATWERT("01.12.2009")) * (B4:B34 < DATWERT("01.01.2010")) * (E4:E34 = I1) * (F4:F34 =$M$1) * (H4:H34))
runterziehen z. B. bis M19

M21: = J2
M22: =SUMMENPRODUKT((B4:B34 >= DATWERT("01.12.2009")) * (B4:B34 < DATWERT("01.01.2010")) * (E4:E34 = I1) * (F4:F34 =$M$21) * (H4:H34))
runterziehen z. B. bis M39

Als nächstes könnte man sich eine sog. "Kreuztabelle" Kategorien X Händler anlegen, aber da das jeweils nur für 2 Merkmale funktioniert und ich nicht weiß, ob Du das überhaupt haben willst, lasse ich das jetzt mal weg.

(Fortsetzung folgt.)

MfG Charlotte

Answer 8

(Fortsetzung)

Und nun kommt schließlich noch das, was Hajo bereits in AW2 gemacht hat, nämlich alles so stricken, dass es auf einem beliebigen anderen Tabellenblatt, z. B. "Kaufdatum" stattfindet und dass man von dort aus auf die Daten in "EK-Bon" zugreift. Die Formeln werden etwas länger, aber eigentlich nicht komplizierter.

Außer dass ich vielleicht noch ein weiteres Tabellenblatt names "Unsichtbar" benutzen würde, auf dem ich die ganzen "Listen" von Kategorien, Händlern usw. unterbringen würde, damit sie 1. woanders nicht stören und 2. nicht so leicht aus Versehen verändert werden.

Um gegenüber dem letzten Stand möglichst wenig zu ändern, schreibe ich wie oben

in das Blatt "Unsichtbar" in
I1: Obst
I2: Gemüse
I3: Milchprodukte

J1: Kaufhof
J2: ALDI
J3: REWE

und dann ebenfalls wie oben in das Blatt "Kaufdatum":

Spalte L - Übersicht nach Kategorien:
L1: =I1
L2: =SUMMENPRODUKT(('EK-Bons'!B3:B33 >= DATWERT("01.12.2009")) * ('EK-Bons'!B3:B33 < DATWERT("01.01.2010")) * ('Unsichtbar'!E3:E33 =$L$1) * ('Unsichtbar'!F3:F33 = J1) * ('EK-Bons'!H3:H33))
runterziehen z. B. bis L19

L21: =I2
L22: =SUMMENPRODUKT(('EK-Bons'!B3:B33 >= DATWERT("01.12.2009")) * ('EK-Bons'!B3:B33 < DATWERT("01.01.2010")) * ('Unsichtbar'!E3:E33 =$L$21) * ('Unsichtbar'!F3:F33 = J1) * ('EK-Bons'!H3:H33))
runterziehen z. B. bis L39 usw.

Spalte M - Übersicht nach Händlern:
M1: =J1
M2: =SUMMENPRODUKT(('EK-Bons'!B3:B33 >= DATWERT("01.12.2009")) * ('EK-Bons'!B3:B33 < DATWERT("01.01.2010")) * ('Unsichtbar'!E3:E33 = I1) * ('Unsichtbar'!F3:F33 =$M$1) * ('EK-Bons'!H3:H33))
runterziehen z. B. bis M19

M21: = J2
M22: =SUMMENPRODUKT(('EK-Bons'!B3:B33 >= DATWERT("01.12.2009")) * ('EK-Bons'!B3:B33 < DATWERT("01.01.2010")) * ('Unsichtbar'!E3:E33 = I1) * ('Unsichtbar'!F3:F33 =$M$21) * ('EK-Bons'!H3:H33))
runterziehen z. B. bis M39

Bei sämtlichen Formeln für L2, L22, M2, M22 usw. müssen natürlich noch die Bereiche angepasst werden. Da wo ich oben (für M2, M22) B4:B34, E4:E34, F4:F34, H4:H34 geschrieben habe, hätte ich besser B3:B33, E3:E33, F3:F33, H3:H33 verwendet, da es sich ja jeweils um dieselben Bereiche handelt und wegen der Verständlichkeit und Logik des Beispiels.

Auch wenn damit erstmal alles beschrieben ist, könnte ich meine Beispieldatei mit wenigen Handgriffen fertig machen und hochladen, wenn das gewünscht wird.

MfG Charlotte

Answer 9

Hallo Inge,

vergiss die Formeln in AW8, da sind mir bei den Bezügen zwei Fehler unterlaufen, mehrmals, immer wieder dieselben. Hier noch einmal richtig:

in das Blatt "Unsichtbar" in
I1: Obst
I2: Gemüse
I3: Milchprodukte

J1: Kaufhof
J2: ALDI
J3: REWE

und dann ebenfalls wie oben in das Blatt "Kaufdatum":

Spalte L - Übersicht nach Kategorien:
L1: =I1
L2: =SUMMENPRODUKT(('EK-Bons'!B$3:B$333 >= DATWERT("01.12.2009")) * ('EK-Bons'!B$3:B$333 < DATWERT("01.01.2010")) * ('EK-Bons'!E$3:E$333 =$L$1) * ('EK-Bons'!F$3:F$333 = Unsichtbar!J1) * ('EK-Bons'!H$3:H$333))
runterziehen z. B. bis L19

L21: =I2
L22: =SUMMENPRODUKT(('EK-Bons'!B$3:B$333 >= DATWERT("01.12.2009")) * ('EK-Bons'!B$3:B$333 < DATWERT("01.01.2010")) * ('EK-Bons'!E$3:E$333 =$L$21) * ('EK-Bons'!F$3:F$333 = Unsichtbar!J1) * ('EK-Bons'!H$3:H$333))
runterziehen z. B. bis L39 usw.

Spalte M - Übersicht nach Händlern:
M1: =J1
M2: =SUMMENPRODUKT(('EK-Bons'!B$3:B$333 >= DATWERT("01.12.2009")) * ('EK-Bons'!B$3:B$333 < DATWERT("01.01.2010")) * ('EK-Bons'!E$3:E$333 = Unsichtbar!I1) * ('EK-Bons'!F$3:F$333 =$M$1) * ('EK-Bons'!H$3:H$333))
runterziehen z. B. bis M19

M21: = J2
M22: =SUMMENPRODUKT(('EK-Bons'!B$3:B$333 >= DATWERT("01.12.2009")) * ('EK-Bons'!B$3:B$333 < DATWERT("01.01.2010")) * ('EK-Bons'!E$3:E$333 = Unsichtbar!I1) * ('EK-Bons'!F$3:F$333 =$M$21) * ('EK-Bons'!H$3:H$333))
runterziehen z. B. bis M39

Und dann wäre da noch die Beispieldatei:
www.file-upload.net/download-2150393/SN2295684_Einkaufsliste.xls.html

MfG Charlotte

Answer 10

Hallo Charlotte,

Jetzt hast du mir aber wirklich 'ne ausführliche Probe-Datei (noch dazu in so kurzer Zeit!) gemacht.
Mit den verschachtelten Formeln hab ich wohl erst mal 'ne ganze Weile zu tun - aber ich werd's angehen :-)
Vielen Dank für deine Mühe. Sollten noch Fragen auftauchen oder auch wenn alles geklappt hat, melde ich mich wieder.

MfG Inge