Office: summewenn() mit 2 Kriterien

Hallo an alle,

habe folgendes Problem:

Habe eine Tabelle mit den Spalten A,B,C,D,E. (Datum, Nettobetrag, UST-Satz, UST-Betrag, Bruttobetrag)

In der Spalte G wandle ich die Datumsangaben z.Bsp. 10.01.13 aus der Spalte "A" in Januar, Februar, März, etc. um.

Nun habe ich im gleichen Arbeitsblatt unter der obigen Tabelle eine weitere Tabelle, ebenfalls mit den Spalten A,B,C,D,E angelegt (Monat: Umsätze 7%: Umsätze 19%: UST 7%: UST 19%).

Unter Monat stehen die Monate: Januar, Februar, März. Ich erreiche nun mit der Formel: =SUMMEWENN($G$2:$G$10;"=Januar";$B$2:$B$10), dass Excel 2003 mir alle Umsätze addiert, die im Januar geschrieben wurden. Leider vermischt er dabei die 7%-Umsätze mit den 19%-Umsätzen, und die benötige ich jeweils in einer eigenen Spalte. Das heißt: Excel soll mir alle im Januar geschriebenen Rechnungen mit 7% UST in die Spalte "B" (Umsätze 7%) addieren, und alle im Januar geschriebenen Rechnungen mit 19% UST in die Spalte "C" (Umsätze 19%)addieren. Für die Monate bis Dezember wird analog verfahren.

Ich habe euch eine Beispieldatei angehängt für's bessere Verständnis und zum direkten ausprobieren :-)

Kann jemand helfen wie ich das umsetzen kann, möglichst nur mit einer Formel oder wenn's nicht anders geht auch mit VBA?

Gruß Horst

:)

 

Hallo Horst,

schau mal ob das so geht

mit einer Summewenn für mehrspaltiges Suchkriterium

für Zelle B15
{=SUMME(WENN((TEXT($A$2:$A$10;"MMMM")&$C$2:$C$10&"%"=$A15&RECHTS(B$14;LÄNGE(B$14)-8));$B$2:$B$10))

Diese {Klammern} werden nicht eingegeben sondern zum Abschluß der Formel durch gleichzeitig Strg Shift Enter erzeugt (statt Enter allein).

« Gruß Peter9 »

 

Hallo Horst,

ohne Array

Tabelle1

	F	G	H	I	J
1	Jahr	Monat	Ust-Satz	Ust	Brutto
2	2013	1	7	214	414
3	2013	1	19	238	438
4	2013	2	7	160,5	310,5
5	2013	2	19	297,5	547,5

verwendete Formeln
Zelle	Formel	Bereich
I2	=SUMMENPRODUKT((MONAT($A:$A)=G2)*(JAHR($A:$A)=F2)*($C:$C=$H2)*(D$ 2:D))
J2	=SUMMENPRODUKT((MONAT($A:$A)=G2)*(JAHR($A:$A)=F2)*($C:$C=$H2)*(E$ 2:E))
I3	=SUMMENPRODUKT((MONAT($A:$A)=G3)*(JAHR($A:$A)=F3)*($C:$C=$H3)*(D$ 2:D))
J3	=SUMMENPRODUKT((MONAT($A:$A)=G3)*(JAHR($A:$A)=F3)*($C:$C=$H3)*(E$ 2:E))
I4	=SUMMENPRODUKT((MONAT($A:$A)=G4)*(JAHR($A:$A)=F4)*($C:$C=$H4)*(D$ 2:D))
J4	=SUMMENPRODUKT((MONAT($A:$A)=G4)*(JAHR($A:$A)=F4)*($C:$C=$H4)*(E$ 2:E))
I5	=SUMMENPRODUKT((MONAT($A:$A)=G5)*(JAHR($A:$A)=F5)*($C:$C=$H5)*(D$ 2:D))
J5	=SUMMENPRODUKT((MONAT($A:$A)=G5)*(JAHR($A:$A)=F5)*($C:$C=$H5)*(E$ 2:E))

 

Hallo Peter9,

zunächst möchte ich mich für deine rasche Hilfe ganz herzlich bedanken. Ich denke dein Konzept scheint mir genau das richtige für mich zu sein. Ich bin seit geraumer Zeit dabei dein Konzept auf mein etwas größere >Bauwerk< anzuwenden. Soweit hat bisher auch alles gut geklappt. Nur will ich das ganze noch modifizieren, indem ich gegebene Skonti in die Datenkomprimierung mit einbinde, sodass sich Umsatz sowie UST entsprechend korrigieren. Das ganze will ich versuchen alleine umzusetzen. Sollte ich noch Fragen haben, ich lasse den Beitrag solange offen, melde ich mich nochmals bei dir.

Gruß Horst

 

Hallo Hajo,

auch bei dir möchte ich mich ganz herzlich bedanken für deine rasche Hilfe. Dein Konzept habe ich noch nicht ausprobiert, weil ich das Konzept von Peter9 als für mich nützlich anwenden kann. Ich werde dein Konzept auf jeden Fall noch behalten und testen, vielleicht kann ich es für eine andere Baustelle nutzen. *Smilie

Gruß Horst

 

Hallo Horst,

Deine Auswertung wäre mit einer Pivot-Tabelle sehr einfach und schnell erstellt - am besten in Verbindung mit dynamischen Datenbereichen, siehe Listen (ab 2007: Tabellen).

Schau Dir mal die Tutorials unter Online-Excel.de an - das ist einfach und schnell an neue Ideen der Auswertung angepasst - allein schon, wenn Dich später mal die Aufteilung nicht nur nach Monaten, sondern auch Jahren interessiert.

Online - Excel: Pivottabelle - Kurzeinstieg
Online - Excel: Pivottabelle - Dynamischer Datenbereich
Online - Excel: Pivottabelle - Datum oder Zeit gruppieren

Grüße
EarlFred

 

Hallo Peter9,

bin gerade an eine Grenze gestoßen: kann ich mit deiner Formel auch ein 3. Kriterium herausfiltern, den Bereich der Skonti zum Bleistift? Wenn ja, wie muss ich die Formel erweitern?*sos

Gruß Horst

 

Hallo EarlFred,

vielen Dank auch dir, für deine Antwort. Ich habe leider bisher mit Pivot-Tabellen noch nie gearbeitet, deshalb lass ich erstmal die Finger weg und gehe das Thema >auf alle Fälle< in der nächsten Zeit an. Ich hoffe, dass ich mit der Formel von Peter9 mein Problem noch lösen kann. Mit 2 Kriterien klappt das auch wirklich gut, jetzt benötige ich noch ein 3. Kriterium zum herausfiltern der in Anspruch genommenen Skonti.

Gruß Horst *confused.gif*

 

Hallo Horst,

ja das müsste gehen kannst Du deine beispiel-Datei mal so erweitern das ich mal sehen kann was abgfragt werden soll bei Skonti und hochladen

Danke

« Gruß Peter9 »

 

Grüße dich Peter9,

anbei die Beispieldatei_2. Die Entsprechende Erläuterung findest Du direkt unter der Tabelle. Vielen Dank im Voraus.

Gruß Horst *smashing

 

Hallo Horst

Du berechnest doch schon die Skonti in G2:G10
Somit reichen 2 Bedingungen und du musst deine Formeln nicht erweitern, sondern an Stelle von B2:B10 einfach G2:G10 eingeben.

Gruss Sepp

 

Grüß dich Sepp,

Du hast recht, ich war gestern Abend wohl so verbissen, dass ich das leider nicht selbst bemerken konnte. Auf jeden Fall bedanke ich mich bei dir *Smilie Manchmal tut ein bisschen Abstand gut.

Würde mich aber dennoch interessieren, wenn die Spalte G:G nicht vorhanden wäre, wie bau ich da ein 3. Kriterium, oder noch mehr ein? Wäre schön zu wissen für die Zukunft.

Gruß Horst *Sun

 

Hallo Horst

Ohne Spalte G brauchst du auch kein drittes Kriterium sondern eine Zusatzberechnung. Statt $B$2:$B$10 musst du $B$2:$B$10*$F$2:$F$10% eingeben.

Gruss Sepp

 

Hallo Horst,

warum eine einfache Pivot-Tabelle erstellen, wenn man doch auch lange Formeln nehmen kann? *wink.gif*

Sicher eine Geschmacksfrage, aber ich finde Hajos Formelvorschlag einfacher. Vielleicht solltest Du Dir den nochmal anschauen?

Grüße
EarlFred

 

Der war gut *biggrin.gif*

Gruß
Norbert