Einleitung
Angeregt durch die Anfrage eines Lesers, die ich zunächst nicht beantworten konnte, ist dieser Beitrag entstanden.
In der Anfrage ging es darum, Flugzeiten zu addieren, wenn zwei Bedingungen in einer anderen Spalte eingehalten werden. Das Problem war, dass die Spalte mit den Flugzeiten auch nichtnumerische Werte enthalten sollten, z.B. in Klammern gesetzt oder mit Buchstaben versehen.
(4:00)
4:00 xz
Der Leser wollte das Problem mit der Funktion SUMMENPRODUKT lösen, hatte aber keinen Erfolg damit.
Doch mal meine Versuche im Detail. Als Zahlenformat ist [hh]:mm zu verwenden.
Summen ohne nichtnumerische Werte und zwei Nebenbedingungen
Die Tabelle könnte etwa so aussehen:
Nehme ich SUMMENPRODUKT
=SUMMENPRODUKT((B24:B42={“QW”.”LK”})*(C24:C42))
erhalte ich als Ergebnis 52:30
Die entsprechenden Zellen sind blau hervorgehoben.
Nehme ich SUMMEWENN
=SUMMEWENN(B24:B42;{“QW”.”LK”};C24:C42)
erhalte ich als Ergebnis 27:00
Das ist nur die Summe der Zeiten, denen die Bedingung “QW” zugeordnet ist.
Selbst wenn die Formel mit Strg+Shift+Enter als Matrixformel eingegeben wird, bleibt das Ergebnis. SUMMEWENN ist daher nicht geeignet.
Nehme ich SUMMEWENNS
=SUMMEWENNS(C24:C42;B24:B42;”QW”;B24:B42;”LK”)
erhalte ich das Ergebnis 0.
Das ist logisch, denn die Abfrage verlangt, dass sowohl “QW” als auch “LK” als Bedingung erfüllt wird und das ist nicht der Fall.
Versuche ich, das Kriterium_1 mit der ODER-Funktion zu definieren, geht auch das nicht.
=SUMMEWENNS(C24:C42;B24:B42;ODER(“QW”;”LK”))
=0
Nehme ich SUMME in Verbindung mit WENN
={SUMME(WENN(B24:B42={“QW”.”LK”};C24:C42))}
erhalte ich mit 52:30 wieder das richtige Ergebnis. Die Formel ist jedoch mit Strg+Shift+Enter als Matrixformel abzuschließen.
Zusammengefasst ist festzuhalten, dass die Funktionen SUMMENPRODUKT und SUMME in Verbindung mit WENN geeignet sind, Flugzeiten bei zwei Nebenbedingungen zu addieren.
Summen mit nichtnumerischen Werten und zwei Nebenbedingungen
Die Flugzeiten wurden in drei Zellen der Tabelle nichtnumerisch gestaltet.
Nehme ich SUMMENPRODUKT
=SUMMENPRODUKT((B85:B103={“QW”.”LK”})*(C85:C103))
erhalte ich als Ergebnis #WERT!
Warum?
Vergleiche ich die Werte in B85:B103 mit den Bedingungen “QW” bzw. “LK”, ergibt diese Abfrage entweder WAHR oder FALSCH.
Multipliziere ich WAHR oder FALSCH nun mit den nichtnumerischen Werten, wie es SUMMENPRODUKT tut, erhalte ich #WERT!.
Die Summe all dieser Produkte ergibt dann auch #WERT!
In nachstehender Tabelle habe ich das zusammengefasst.
Nehme ich SUMMEWENN
=SUMMEWENN(B85:B103;{“QW”.”LK”};C85:C103)
erhalte ich als Ergebnis 14:45
Das ist die Summe aller numerischen Zeiten, für die “QW” zutrifft, “LK” wird ignoriert.
Nehme ich SUMMEWENNS
=SUMMEWENNS(C85:C103;B85:B103;”QW”;B85:B103;@LK”)
erhalte ich als Ergebnis 0
Das ist wiederum logisch, denn die Abfrage verlangt, dass sowohl “QW” als auch “LK” als Bedingung erfüllt wird, das ist nicht der Fall.
Nehme ich SUMME in Verbindung mit WENN
={SUMME(WENN(B85:B103=”QW”.”LK”};C85:C103))}
=38:00
erhalte ich das richtige Ergebnis.
Die Formel ist wieder mit Strg+Shift+Enter als Matrixformel abzuschließen. Die entsprechenden Zellen sind blau hervorgehoben.
Damit kann die Leserfrage beantwortet werden. Die Lösung kann nicht mit der Funktion SUMMENPRODUKT herbeigeführt werden.
Zu verwenden ist eine Kombination aus den Funktionen SUMME und WENN, wie zum Schluss gezeigt.