Hallo
Ich habe eine Frage zur Anwendung der Formel SUMMENPRODUKT()
Da die Tabellendarstellung mit Code in FAQ:2363 hier in diesem „Kopfbeitrag“ nicht funktioniert, fahre ich gleich weiter im nächsten Beitrag
Niclaus
Summenprodukt - Excel 2010
Hallo. Ich habe folgende Tabelle:
Tabelle1
│ A │ B │ C │ D │ E │ F │ G │
──┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
1 │ Aufw1 │ Krit1 │ Aufw2 │ Krit2 │ Aufw3 │ Krit3 │ Total │
──┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
2 │ 5 │ 1 │ 10 │ 0 │ 20 │ 1 │ 25 │
──┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
3 │ Aufw │ Krit │ │ │ │ │ │
──┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
4 │ 5 │ 1 │ │ │ │ │ │
──┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
5 │ 10 │ 0 │ │ │ │ │ │
──┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
6 │ 20 │ 1 │ │ │ │ │ │
──┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
7 │ Total │ 25 │ │ │ │ │ │
──┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘
Benutzte Formeln:
B7: =SUMMENPRODUKT((A4:A6)\*(B4:B6=1))
G2: =SUMME(A2\*(B2=1);C2\*(D2=1);E2\*(F2=1))
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Meine Frage betrifft die Zeile 2: Kann ich für die Berechnung in G2 auch mit der Formel SUMMENPRODUKT() arbeiten? Ich stelle mir das etwa so vor:
=SUMMENPRODUKT({A2.C2.E2};{(B2.D2.F2)=1})
Was natürlich nicht funktioniert. Oder gibt es eine andere, elegantere Lösung für die Berechnung in G2?
Kann mir jemand Auskunft geben?
Vielen Dank und Grüsse Niclaus
Hallo,
bei dem Aufbau genügt:
=SUMMEWENN(B2:F2;1;A2:E2)
musst halt nur auf den versetzen Bereich achten!
VG
bei dem Aufbau genügt:
=SUMMEWENN(B2:F2;1;A2:E2)
musst halt nur auf den versetzen Bereich achten!
Hallo 16 BIT
Super! Vielen Dank!
Aber die Begehrlichkeit wächst! Die Kriterien in der Tabelle sind komplexer: Es sollen die Aufwände (Aufw1, Aufw2 usw.) nur dann summiert werden, wenn der Monat der entsprechenden Datümer (Datum1, Datum2 usw.) dem aktuellen Monat entspricht.
Es kommt dazu, dass in der Zeile 2 nicht nur 3 Aufwände/Datümer zu berechnen sind sondern 30. Für einen Vorschlag, wie man die Formel in G2 (ohne VBA) „vereinfachen“ könnte, bin ich sehr dankbar. - Die Tabelle darf leider nicht „umgestaltet“ werden.
Grüsse Niclaus
Hier noch die Tabelle:
Tabellenblatt
│ A │ B │ C │ D │ E │ F │ G │ H │
──┼───────┼──────────┼───────┼──────────┼───────┼──────────┼───────┼───────┤
1 │ Aufw1 │ Datum1 │ Aufw2 │ Datum2 │ Aufw3 │ Datum3 │ Total │ aktMt │
──┼───────┼──────────┼───────┼──────────┼───────┼──────────┼───────┼───────┤
2 │ 5 │ 15.10.14 │ 10 │ 15.11.14 │ 20 │ 15.10.14 │ 25 │ 10 │
──┴───────┴──────────┴───────┴──────────┴───────┴──────────┴───────┴───────┘
Benutzte Formeln:
G2: =SUMME(A2\*(MONAT(B2)=$H$2);C2\*(MONAT(D2)=H2);E2\*(MONAT(F2)=H2))
H2: =MONAT(HEUTE())
Zahlenformate der Zellen im gewählten Bereich:
A1:A2,B1,C1:C2,D1,E1:E2,F1,G1:G2,H1:H2 - Zahlenformat: Standard
B2,D2,F2 - Zahlenformat: TT.MM.JJ;@
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Hiho!
Die Tabelle darf nicht verändert werden - heißt das, optisch oder insgesamt?
Bei optisch, kann man ja theoretisch hilfsspalten ausblenden lassen
bei insgesamt evtl. auf dem 2. Tabellenblatt mit Hilfsspalte(n) arbeiten.
Mittels diesen Hilfsspalten schonmal die Kriterien soweit rausfiltern, dass man am Ende nurnoch eine „normale“ Summe ziehen muss.
Grüße
René
Hiho!
Die Tabelle darf nicht verändert werden - heißt das, optisch
oder insgesamt?Bei optisch, kann man ja theoretisch hilfsspalten ausblenden
lassen
bei insgesamt evtl. auf dem 2. Tabellenblatt mit
Hilfsspalte(n) arbeiten.Mittels diesen Hilfsspalten schonmal die Kriterien soweit
rausfiltern, dass man am Ende nurnoch eine „normale“ Summe
ziehen muss.
Grüezi René
Tabelle verändern: Am besten überhaupt nicht. Ich brauche sie nicht für mich selber, sondern für verschiedene Sachbearbeiter. Aus Erfahrung weiss ich, dass es mit (ausgeblendeten) Hilfsspalten und Hilfsblättern wegen „Falsch-Benutzung“ zu Fehlern kommt.
Langsam merke ich, am einfachsten werde ich es doch mit VBA machen mit einer Function.
Danke für Dein Mit-Denken und viele Grüsse Niclaus
Hallo,
kommt halt auf die Datenmenge dann an…teste mal:
{=SUMME(WENN(MONAT(B2:F2)=H2;A2:E2))}
MATRIXFORMEL!!
VG
kommt halt auf die Datenmenge dann an…teste mal:
{=SUMME(WENN(MONAT(B2:F2)=H2;A2:E2))}
Hallo 16BIT
Es ist super mit dieser Formel! Ganz herzlichen Dank!
Der erste Gedanke beim Lesen der Formel (ähnlich schon bei der Formel in Deinem Beitrag vom 31.10.2014 12:42 h) war: Das kann doch nie und nimmer funktionieren, wenn zwei Datensätze sich so überschneiden! - Aber was 16BIT schreibt, stimmt sicher. Und tatsächlich: alles ok.
Darf ich Dich um Auskunft bitten zum Verständnis dieser Formel: Kann man sagen, wenn die Datenbereiche so dargestellt werden (B2:F2 und A2:E2), dann ist Excel so super-schlau, und überspringt jedes zweite Element? Es nimmt also zum Kriterienvergleich nur B2, D2, F2 und als Summen-Bereich nur A2, C2, E2.
Ist diese Anwendung irgendwo dokumentiert, oder hast Du das rausgekriegt?
Noch einmal danke schön und freundliche Grüsse
Niclaus
Hallo Niclaus,
Kann man sagen, wenn die Datenbereiche so dargestellt
werden (B2:F2 und A2:E2), dann ist Excel so super-schlau, und
überspringt jedes zweite Element?
Nein, schlau ist XL nicht. Es macht halt nur seine Arbeit die man ihm vorgibt.
Es schaut halt erst in die eine Matrix ob dort die gesuchten Kriterien vorhanden sind und wenn ja, gleicht es diese mit der anderen Matrix ab.
Es nimmt also zum
Kriterienvergleich nur B2, D2, F2 und als Summen-Bereich nur
A2, C2, E2.
Nein.
Es nimmt als Kriterienvergleich B2 bis F2 und als Summenbereich A2 bis E2.
Du hättest als Summenbereich auch alle anderen Bereiche anbieten können (z.B. F100 bis J100). Die Bereiche müssen jedoch nur gleich groß sein.
Wenn Du in die Formelauswertung schaust, steht dann es in etwas so drin:
SUMME(WENN({WAHR.FALSCH.FALSCH.FALSCH.WAHR};{5.41927.10.41958.20}))
Ist diese Anwendung irgendwo dokumentiert, oder hast Du das
rausgekriegt?
Nun die XL-Hilfe ist nicht immer hilfreich, jedoch gibt es zu SUMMEWENN() einen guten Eintrag zum Summenbereich:
Summe_Bereich sind die tatsächlich zu addierenden Zellen, wenn die Inhalte ihrer entsprechenden Zellen in Bereich mit den Suchkriterien übereinstimmen.
Ich hoffe, ich konnte etwas Licht ins Dunkle bringen!
VG