Summe unter Bedingungen bei nichtnumerischen Werten

 

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:

Nichtnumerisch1

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

Nichtnumerisch2

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.

Nichtnumerisch3

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.

Advertisements

Prüfung, ob vertikale und horizontale Summen identisch sind

Enthält eine Tabelle mehrere Spalten und mehrere Zeilen, Zeilensummen und Spaltensummen sowie eine Gesamtsumme, vertraue ich oftmals darauf, alle Formeln zur Summierung richtig eingetragen zu haben.

Wie darüber die Kontrolle behalten werden kann, ist Gegenstand dieses Beitrages.

Die folgende Tabelle zeigt beispielsweise die produzierten Mengen aus sechs Werken für die ersten sechs Monate eines Jahres.

Spaltensumme1

Die Zellen B11:G11 sollten richtigerweise die Mengen der Werke 1 bis 6 addieren.

Die Zellen H5:H10 sollten ebenso richtig die Mengen der Monate Januar bis Juni addieren.

Und schließlich soll in Zelle H11 entweder die Summe von B11:G11 oder die Summe der Werte aus H5:H10 gebildet werden.

Die Summe aus B11:G11 und die Summe aus H5:H10 müssen identisch sein. In H11 kann aber nur eine Formel eingetragen werden. So kann es durchaus passiert sein, das irgendeine Summe einen Wert aus der Spalte oder aus der Zeile „vergessen“ hat. Ein solcher Fehler ist aber nicht sofort sichtbar.

Wie lässt sich im Arbeitsblatt eine Kontrolle darüber einflechten?

Eine Möglichkeit ist, irgendwo auf dem Arbeitsblatt, z.B. in I11, eine der folgenden Formeln einzutragen:

  • =WENN(SUMME(H5:H10)=SUMME(B11:G11);“OK“;“Fehler“)
  • =IDENTISCH(SUMME(H5:H10);SUMME(B11:G11))
  • =SUMME(H5:H10)=SUMME(B11:G11)

Die zweite und dritte Formel geben als Ergebnis ein „WAHR“ oder ein „FALSCH“ heraus.

Darüber hinaus kann durch eine bedingte Formatierung die Zelle H11 manipuliert werden, so dass sofort ersichtlich ist, ob die Summenformeln richtig sind.

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)<>SUMME(B11:G11)

formatieren / ausfüllen: rot

und als zweite Formel:

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)=SUMME(B11:G11)

formatieren / ausfüllen: grün

Ergänzend kann unterhalb von H11, z.B. in H13, noch eine kurze Erläuterung des Fehlers konstruiert werden. Dazu kannst du diese Formel verwenden:

=WENN(SUMME(H5:H10)<SUMME(B11:G11);“Zeilensumme ist kleiner“;WENN(SUMME(B11:G11)<SUMME(H5:H10);“Spaltensumme ist kleiner“;“OK“))

Folgende Tabelle enthält in der Summe des Monats April (gelb markiert) einen Fehler. Und der wird angezeigt:

Spaltensumme2

Wenn ein Fehler vorhanden ist und dies auch angezeigt wird, müssen die Zeilen-, Spalten- und Gesamtsummen nur überprüft und korrigiert werden. Dann ist H11 wieder grün.

So nutzt du die Funktion SUMME in Excel

Summen können manuell oder mit dem Taschenrechner berechnet werden. Wer Excel-User ist, kann es sich einfacher machen und die Summenfunktionen nutzen.

Die Summenfunktionen sind im Menü Formeln in der Kategorie Mathematik und Trigonometrie zu finden.

Von den sieben Summen-Funktionen will ich in den nächsten drei Beiträgen je eine erläutern. In diesem Beitrag geht es um die Funktion SUMME.

Weiterlesen „So nutzt du die Funktion SUMME in Excel“

7 Wege in Excel, Summen zu bilden

Das Gute an Excel ist, dass es immer mehrere Möglichkeiten gibt, zum Ziel zu kommen. Dazu habe ich mich vor einiger Zeit schon mal in meinem Gastartikel „Rechnet Excel richtig?“ auf www.tabellenexperte.de geäußert.

Heute will ich Dir ein weiteres Beispiel in dieser Richtung liefern. Hast Du Dich eigentlich schon einmal gefragt, auf wie vielen Wegen in Excel die Addition von Werten möglich ist?

Weiterlesen „7 Wege in Excel, Summen zu bilden“