Office: (Office 2010) Summenprodukt

Hallo zusammen,

auf den letzten Metern meiner Excel Auswertung verlassen mich meine Excel Fertigkeiten (oder die Fähigkeit, die passende Lösung zu finden).

Die Datenquelle befindet sich wie unten zu sehen in den Spalten A-F:
Spalte A: Datum
Spalte B: Typ
Spalte C: Identifier
Spalte D: Anzahl
Spalte E: Name
Spalte F: Ort

Die Berechnung, um die es geht in den Spalten G-I:
Zelle H5: Summe der Tage im Monat Februar mit Ort = "Zentrale 1"
(Zellen H5 - I7 analog)

Allerdings werden in der Berechnung die Tage addiert, die mit gleichem Identifier (Spalte C) in der Quelle stehen, das Ergebnis in Zelle H5 ist 16. Das soll vermieden werden, so dass folgende Angabe in H11 steht:
Summe der Tage im Monat Februar mit eindeutigem Identifier und Ort = "Zentrale 1"
Das Ergebnis sollte also in diesem Fall 9 sein.


Ich bekomme die Formel nicht hin, kann bitte jemand helfen?
Danke!


Christoph



CSV

	A	B	C	D	E	F	G	H	I
1	02.03.2015	Auszahlung
2	02.03.2015	Buchung	87FG7L	5	Hans	Zentrale 1
3	16.02.2015	Auszahlung
4	16.02.2015	Buchung	KW5FTT	3	Dieter	Zentrale 1	IST	Februar	März
5	16.02.2015	Buchung	KXWEF4	2	Peter	Zentrale 1	Zentrale 1	16	5
6	16.02.2015	Auszahlung					Zentrale 2	1	0
7	16.02.2015	Buchung	KW5FTT	3	Dieter	Zentrale 1	Zentrale 3	5	0
8	16.02.2015	Buchung	KXWEF4	2	Peter	Zentrale 1
9	09.02.2015	Auszahlung
10	09.02.2015	Buchung	99HH8H	5	Hans	Zentrale 3	SOLL	Februar	März
11	06.02.2015	Auszahlung					Zentrale 1	9	5
12	06.02.2015	Buchung	SA4HWZ	3	Maria	Zentrale 1	Zentrale 2	1	0
13	06.02.2015	Auszahlung					Zentrale 3	5	0
14	06.02.2015	Buchung	SA4HWZ	3	Maria	Zentrale 1
15	01.02.2015	Auszahlung
16	01.02.2015	Buchung	Z8M8DK	1	Jens	Zentrale 2

Formeln der Tabelle

Zelle Formel H5 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(H)); --(CSV!$F:$F="Zentrale 1");CSV!$D:$D) I5 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(I)); --(CSV!$F:$F="Zentrale 1");CSV!$D:$D) H6 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(H)); --(CSV!$F:$F="Zentrale 2");CSV!$D:$D) I6 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(I)); --(CSV!$F:$F="Zentrale 2");CSV!$D:$D) H7 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(H)); --(CSV!$F:$F="Zentrale 3");CSV!$D:$D) I7 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(I)); --(CSV!$F:$F="Zentrale 3");CSV!$D:$D) Excel Tabellen im Web darstellen >> :)   OfficeUser, 17. Februar 2015 #1 TINKER BELL Tinker Bell Hallo Schau dir mal diese Formel an: http://www.excelformeln.de/formeln.html?welcher=334 Gruss, Tinker Bell   Tinker Bell, 19. Februar 2015 #2 ETAGENYETI2 etagenyeti2 Hallo Tinker Bell, auf die Formel bin ich bei meiner Recherche auch gestoßen... Ich bekomme sie aber nicht auf meinen Fall angepasst. Irgendwo hakts da bei mir. Kriegst Du das hin? Viele Grüße und tausend Dank Christoph   etagenyeti2, 19. Februar 2015 #3 MüCKE mücke Summenprodukt Moin Christoph, müsste in H5 nicht als Ergebis eine 6 stehen? Nachstehende Formel steht in H5 und kann nach unten bzw. rechts kopiert werden. PHP:          =SUMMENPRODUKT(1*(VERGLEICH($A$1:$A$16&$F$1:$F$16;$A$1:$A$16&$F$1:$F$16;0)=ZEILE($1:$16)*(MONAT($A$1:$A$16)=MONAT(1&H$4))*($F$1:$F$16=$G5));$D$1:$D$16)   mücke, 19. Februar 2015 #4 OFFICEUSER OfficeUser Neuer User Moin :-) Danke für Deine Hilfe! Ich hab es mir nochmal angesehen und ich habe mich verschrieben. Sorry! In H5 müsste eine 8 stehen. Hier die Erklrärung zu jedem Wert in Spalte D warum er berücksichtigt werden soll und warum nicht. Vielleicht wird es so ganz deutlich. D2: Ereignis aus März, nicht Februar (Spalte A) D4: zählt D5: zählt D7: Dublikat zu D4 (Spalte C ist identisch) D8: Dublikat zu D5 (Spalte C ist identisch) D10: Ort ist Zentrale 3, nicht Zentrale 1 (Spalte F) D12: zählt D14: Dublikat zu D12 (Spalte C ist identisch) D16: Ort ist Zentrale 2, nicht Zentrale 1 (Spalte F) Gibt es eine Lösung, die die Anzahl Zeilen offen lässt? Die Quelldaten können sehr lange werden und sind nicht editierbar, da sie wieder weiterverarbeitet werden. Hier nochmal, die zu addierenden Werte pink hinterlegt, die anderen fallen aus oben genannten Gründen raus: CSV   A B C D E F G H I 1 02.03.2015 Auszahlung               2 02.03.2015 Buchung 87FG7L 5 Hans Zentrale 1       3 16.02.2015 Auszahlung               4 16.02.2015 Buchung KW5FTT 3 Dieter Zentrale 1 IST Februar März 5 16.02.2015 Buchung KXWEF4 2 Peter Zentrale 1 Zentrale 1 16 5 6 16.02.2015 Auszahlung         Zentrale 2 1 0 7 16.02.2015 Buchung KW5FTT 3 Dieter Zentrale 1 Zentrale 3 5 0 8 16.02.2015 Buchung KXWEF4 2 Peter Zentrale 1       9 09.02.2015 Auszahlung               10 09.02.2015 Buchung 99HH8H 5 Hans Zentrale 3 SOLL Februar März 11 06.02.2015 Auszahlung         Zentrale 1 8 5 12 06.02.2015 Buchung SA4HWZ 3 Maria Zentrale 1 Zentrale 2 1 0 13 06.02.2015 Auszahlung         Zentrale 3 5 0 14 06.02.2015 Buchung SA4HWZ 3 Maria Zentrale 1       15 01.02.2015 Auszahlung               16 01.02.2015 Buchung Z8M8DK 1 Jens Zentrale 2       Formeln der Tabelle Zelle Formel H5 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(H)); --(CSV!$F:$F="Zentrale 1");CSV!$D:$D) I5 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(I)); --(CSV!$F:$F="Zentrale 1");CSV!$D:$D) H6 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(H)); --(CSV!$F:$F="Zentrale 2");CSV!$D:$D) I6 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(I)); --(CSV!$F:$F="Zentrale 2");CSV!$D:$D) H7 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(H)); --(CSV!$F:$F="Zentrale 3");CSV!$D:$D) I7 =SUMMENPRODUKT(--(MONAT(CSV!$A:$A)=MONAT(I)); --(CSV!$F:$F="Zentrale 3");CSV!$D:$D) Excel Tabellen im Web darstellen >>   OfficeUser, 19. Februar 2015 #5 OFFICEUSER OfficeUser Neuer User Hallo, Ich hab mich mal dran versucht: Tabelle2   A B C D E F G H I J K L M N O P 1             Hilfsspalte1 Hilfsspalte2           IST Februar März 2 02.03.2015 Auszahlung         März WAHR           Zentrale 1 8 5 3 02.03.2015 Buchung 87FG7L 5 Hans Zentrale 1 März87FG7LZentrale 1 WAHR           Zentrale 2 1 0 4 16.02.2015 Auszahlung         Februar WAHR           Zentrale 3 5 0 5 16.02.2015 Buchung KW5FTT 3 Dieter Zentrale 1 FebruarKW5FTTZentrale 1 WAHR                 6 16.02.2015 Buchung KXWEF4 2 Peter Zentrale 1 FebruarKXWEF4Zentrale 1 WAHR                 7 16.02.2015 Auszahlung         Februar FALSCH           SOLL Februar März 8 16.02.2015 Buchung KW5FTT 3 Dieter Zentrale 1 FebruarKW5FTTZentrale 1 FALSCH           Zentrale 1 8 5 9 16.02.2015 Buchung KXWEF4 2 Peter Zentrale 1 FebruarKXWEF4Zentrale 1 FALSCH           Zentrale 2 1 0 10 09.02.2015 Auszahlung         Februar FALSCH           Zentrale 3 5 0 11 09.02.2015 Buchung 99HH8H 5 Hans Zentrale 3 Februar99HH8HZentrale 3 WAHR                 12 06.02.2015 Auszahlung         Februar FALSCH                 13 06.02.2015 Buchung SA4HWZ 3 Maria Zentrale 1 FebruarSA4HWZZentrale 1 WAHR                 14 06.02.2015 Auszahlung         Februar FALSCH                 15 06.02.2015 Buchung SA4HWZ 3 Maria Zentrale 1 FebruarSA4HWZZentrale 1 FALSCH                 16 01.02.2015 Auszahlung         Februar FALSCH                 17 01.02.2015 Buchung Z8M8DK 1 Jens Zentrale 2 FebruarZ8M8DKZentrale 2 WAHR                 Formeln der Tabelle Zelle Formel G2 =TEXT(A2;"MMMM")&C2&F2 H2 =VERGLEICH(G2;$G:$G;0)=ZEILE(A1) O2 =SUMMENPRODUKT((TEXT($A:$A;"MMMM")=O)*($F:$F=$N2)*($D:$D)*($H:$H)) P2 =SUMMENPRODUKT((TEXT($A:$A;"MMMM")=P)*($F:$F=$N2)*($D:$D)*($H:$H)) Excel Tabellen im Web darstellen >>   OfficeUser, 19. Februar 2015 #6 ETAGENYETI2 etagenyeti2 Hallo, vielen Dank schonmal aber es muss leider ohne Hilfsspalte gehen. Die Ergebnisse von Dir, ransi, stimmen auf jeden Fall (und ich hab keine Ahnung wieso). Ich hab es jetzt wieder stundenlang probiert ohne Hilfsspalte hinzubekommen aber ich schaffs nicht... Ransi, hast Du eine Idee ohne Hilfsspalte? Falls es hilft: Die Auswertung (in Deiner Tabelle Spalten N-P) ist später in einem anderen Tabellenblatt als die Quelle (in Deiner Tabelle Spalten A-H). Gruß Christoph   etagenyeti2, 19. Februar 2015 #7 MüCKE mücke Summenprodukt Moin Christoph, anbei die gewünschte Anpassung. Habe den Bereich mal auf 100 Zeilen erweitert, geht natürlich auch noch weiter *wink.gif* =SUMMENPRODUKT(1*(VERGLEICH($A$1:$A$100&$C$1:$C$100&$F$1:$F$100;$A$1:$A$100&$C$1:$C$100&$F$1:$F$100;0)=ZEILE($1:$100)*(MONAT($A$1:$A$100)=MONAT(1&H$4))*($F$1:$F$100=$G5));$D$1:$D$100)   mücke, 19. Februar 2015 #8 OFFICEUSER OfficeUser Neuer User Hallo Dirk, nochmals vielen Dank. Bei mir kommt aber überall 0 raus. Mache ich was falsch? CSV   A B C D E F G H I 1 02.03.2015 Auszahlung               2 02.03.2015 Buchung 87FG7L 5 Hans Zentrale 1       3 16.02.2015 Auszahlung               4 16.02.2015 Buchung KW5FTT 3 Dieter Zentrale 1 IST Februar März 5 16.02.2015 Buchung KXWEF4 2 Peter Zentrale 1 Zentrale 1 0 0 6 16.02.2015 Auszahlung         Zentrale 2 0 0 7 16.02.2015 Buchung KW5FTT 3 Dieter Zentrale 1 Zentrale 3 0 0 8 16.02.2015 Buchung KXWEF4 2 Peter Zentrale 1       9 09.02.2015 Auszahlung               10 09.02.2015 Buchung 99HH8H 5 Hans Zentrale 3 SOLL Februar März 11 06.02.2015 Auszahlung         Zentrale 1 8 5 12 06.02.2015 Buchung SA4HWZ 3 Maria Zentrale 1 Zentrale 2 1 0 13 06.02.2015 Auszahlung         Zentrale 3 5 0 14 06.02.2015 Buchung SA4HWZ 3 Maria Zentrale 1       15 01.02.2015 Auszahlung               16 01.02.2015 Buchung Z8M8DK 1 Jens Zentrale 2       Formeln der Tabelle Zelle Formel H5 =SUMMENPRODUKT(1*(VERGLEICH($A:$A0&$C:$C0&$F:$F0;$A:$A0&$C:$C0&$F:$F0;0)=ZEILE(:0)*(MONAT($A:$A0)=MONAT(1&H))*($F:$F0=$G5));$D:$D0) I5 =SUMMENPRODUKT(1*(VERGLEICH($A:$A0&$C:$C0&$F:$F0;$A:$A0&$C:$C0&$F:$F0;0)=ZEILE(:0)*(MONAT($A:$A0)=MONAT(1&I))*($F:$F0=$G5));$D:$D0) H6 =SUMMENPRODUKT(1*(VERGLEICH($A:$A0&$C:$C0&$F:$F0;$A:$A0&$C:$C0&$F:$F0;0)=ZEILE(:0)*(MONAT($A:$A0)=MONAT(1&H))*($F:$F0=$G6));$D:$D0) I6 =SUMMENPRODUKT(1*(VERGLEICH($A:$A0&$C:$C0&$F:$F0;$A:$A0&$C:$C0&$F:$F0;0)=ZEILE(:0)*(MONAT($A:$A0)=MONAT(1&I))*($F:$F0=$G6));$D:$D0) H7 =SUMMENPRODUKT(1*(VERGLEICH($A:$A0&$C:$C0&$F:$F0;$A:$A0&$C:$C0&$F:$F0;0)=ZEILE(:0)*(MONAT($A:$A0)=MONAT(1&H))*($F:$F0=$G7));$D:$D0) I7 =SUMMENPRODUKT(1*(VERGLEICH($A:$A0&$C:$C0&$F:$F0;$A:$A0&$C:$C0&$F:$F0;0)=ZEILE(:0)*(MONAT($A:$A0)=MONAT(1&I))*($F:$F0=$G7));$D:$D0) Excel Tabellen im Web darstellen >>   OfficeUser, 19. Februar 2015 #9 CHRIS-KAISER chris-kaiser Hallo Christoph, warum nicht einfach vor der Auswertung die Duplikate entfernen? ggf. vorher noch die Tabelle kopieren - Datenbereich markieren -> Daten -> Duplikate entfernen -> gew. Spalten auswählen -> Auswertung machen. Oder geht das nicht?   chris-kaiser, 19. Februar 2015 #10 ETAGENYETI2 etagenyeti2 Leider nicht so einfach... irgendwo in späteren Spalten unterscheiden sich die Werte dann doch. Die Daten sind leider nur bei den relevaten Bereichen redundant.   etagenyeti2, 19. Februar 2015 #11 MüCKE mücke Moin Christoph, bei mir steht der Monat in Zelle H4 als Text, also Februar! Was steht bei Dir ... ein Datum?   mücke, 19. Februar 2015 #12 CHRIS-KAISER chris-kaiser Summenprodukt Hi, das macht doch nichts, dann sind es ja keine Duplikate und bleiben ja erhalten *Smilie   chris-kaiser, 19. Februar 2015 #13 OFFICEUSER OfficeUser Neuer User Hi Dirk, stimmt, bei mir steht da ein Datum. Nun ist es gelöst, siehe unten. Wenn noch jemand loswerden kann, dass man sich nicht auf eine bestimmte Anzahl Zeilen festlegen muss (ich arbeite gerne mit ganzen Spalten) wäre es die perfekte Lösung. Hier der Stand der Lösung: CSV   A B C D E F G H I J 1 02.03.2015 Auszahlung                 2 02.03.2015 Buchung 87FG7L   5 Hans Zentrale 1       3 16.02.2015 Auszahlung                 4 16.02.2015 Buchung KW5FTT   3 Dieter Zentrale 1 IST Februar März 5 16.02.2015 Buchung KXWEF4   2 Peter Zentrale 1 Zentrale 1 8 5 6 16.02.2015 Auszahlung           Zentrale 2 1 0 7 16.02.2015 Buchung KW5FTT   3 Dieter Zentrale 1 Zentrale 3 5 0 8 16.02.2015 Buchung KXWEF4   2 Peter Zentrale 1       9 09.02.2015 Auszahlung                 10 09.02.2015 Buchung 99HH8H   5 Hans Zentrale 3 SOLL Februar März 11 06.02.2015 Auszahlung           Zentrale 1 8 5 12 06.02.2015 Buchung SA4HWZ   3 Maria Zentrale 1 Zentrale 2 1 0 13 06.02.2015 Auszahlung           Zentrale 3 5 0 14 06.02.2015 Buchung SA4HWZ   3 Maria Zentrale 1       15 01.02.2015 Auszahlung                 16 01.02.2015 Buchung Z8M8DK   1 Jens Zentrale 2       Formeln der Tabelle Zelle Formel I5 =SUMMENPRODUKT((VERGLEICH($A:$A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;$A: $A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;)=ZEILE(A:A99))*$E:$E$9999*(MONAT($A:$A99)=MONAT(I))*($G:$G99=$H5)) J5 =SUMMENPRODUKT((VERGLEICH($A:$A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;$A: $A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;)=ZEILE(B:B99))*$E:$E$9999*(MONAT($A:$A99)=MONAT(J))*($G:$G99=$H5)) I6 =SUMMENPRODUKT((VERGLEICH($A:$A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;$A: $A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;)=ZEILE(A:A99))*$E:$E$9999*(MONAT($A:$A99)=MONAT(I))*($G:$G99=$H6)) J6 =SUMMENPRODUKT((VERGLEICH($A:$A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;$A: $A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;)=ZEILE(B:B99))*$E:$E$9999*(MONAT($A:$A99)=MONAT(J))*($G:$G99=$H6)) I7 =SUMMENPRODUKT((VERGLEICH($A:$A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;$A: $A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;)=ZEILE(A:A99))*$E:$E$9999*(MONAT($A:$A99)=MONAT(I))*($G:$G99=$H7)) J7 =SUMMENPRODUKT((VERGLEICH($A:$A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;$A: $A99&$B:$B99&$C:$C99&$E:$E99&$G:$G99;)=ZEILE(B:B99))*$E:$E$9999*(MONAT($A:$A99)=MONAT(J))*($G:$G99=$H7)) Excel Tabellen im Web darstellen >>   OfficeUser, 20. Februar 2015 #14 ETAGENYETI2 etagenyeti2 Stimm auch wieder... allerdings möchte ich die Quelle nicht verändern, da die von anderen wieder weiterverarbeitet wird und wenn ich da was tue zerschieße ich vielleicht deren Berechnungen.   etagenyeti2, 20. Februar 2015 #15 (Du musst angemeldet oder registriert sein, um eine Antwort erstellen zu können.) Ignorierte Inhalte anzeigen Seite 1 von 2 Thema: Summenprodukt Die Seite wird geladen... Summenprodukt - Similar Threads - Summenprodukt Summenprodukt als Vergleich zu Pivot in Microsoft Excel Hilfe Summenprodukt als Vergleich zu Pivot: Hallo Zusammen, ich habe eine kurze Frage. Ich habe eine Tabelle in der von den Quelldaten, ca. 35000 Zeilen, Pivots erstellt werden. Als Vergleich würde ich gerne Formeln neben die Pivotwerte... Summenprodukt vereinfachen in Microsoft Excel Hilfe Summenprodukt vereinfachen: Hallo, ich habe folgende Summenprodukt-Formel, die ich gerne vereinfachen möchte: =SUMMENPRODUKT (B10:X10=B1+B10:X10=B3+B20:X20=B1+B20:X20=B3+[...]+B200:X200=B1+B200:X200=B3) Ich simuliere... Zaehlenwenns mit mehreren Kriterien? in Microsoft Excel Hilfe Zaehlenwenns mit mehreren Kriterien?: Hallo Zusammen, ich habe in einer Tabelle eine Kundennummer und in einer anderen alle Lieferungen an diesen Kunden inkl der Lieferbedingungen pro Lieferung. Ich wüsste gerne, ob es zu einem... Office 365; Summenprodukt in Microsoft Excel Hilfe Office 365; Summenprodukt: Moinmoin zusammen, ich bekomme den Fehler #WERT! nicht weg. Die Formel lautet: =SUMMENPRODUKT((MONAT($B$11:$B$36)=MONAT($N$11))*($E$11:$E$36)) Damit möchte ich lediglich geleistete Minuten pro... Summewenns mit Datum und Zwei Buchstaben eingrenzen in Microsoft Excel Hilfe Summewenns mit Datum und Zwei Buchstaben eingrenzen: Guten Abend Zusammen, ich versuche aus einer Tabelle mit Summewenns (sumifs) die Beträge vom Kalendermonat abweichend zu errechnen. Das funktioniert mittlerweile auch. Da das Datum über den... Summenprodukt mit wenn funktion in Microsoft Excel Hilfe Summenprodukt mit wenn funktion: Hallo zusammen, ich benötige Hilfe bzgl. meiner Berechnung. Ich habe schon vieles ausprobiert........., immer falsches Ergebnis Es soll wie folgt gerechnet werden: wenn unter Preis (Spalte... SUMMEWENN oder SUMMEWENNS zum verzweifeln in Microsoft Excel Hilfe SUMMEWENN oder SUMMEWENNS zum verzweifeln: Hallo zusammen, manchmal ist es viel schwerer als man denkt. Ich verzweifle an einer Formel die mir anfangs ganz einfach zu sein schien aber anscheinend sind meine Excel Kenntnisse nicht... Benutzername oder E-Mail-Adresse: Besitzt du schon ein Benutzerkonto? Nein, erstelle jetzt ein Benutzerkonto. Ja, mein Passwort ist: Hast du dein Passwort vergessen? Angemeldet bleiben Anmelden oder registrieren Suche Nur die Titel durchsuchen Erstellt von: Trenne Benutzernamen durch Kommata. Neuer als: Nur dieses Thema durchsuchen Nur dieses Forum durchsuchen Die Ergebnisse als Themen anzeigen Nützliche Suchen Themen mit aktuellen Beiträgen Mehr Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten. Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Akzeptieren Weitere Informationen... Information ausblenden