Arbeiten mit der Funktion SUMMEWENNS (Teil 1)

In vorangegangenen Beiträgen konntest du etwas über die Funktionen SUMME und SUMMEWENN erfahren.

In diesem Beitrag geht es um die Funktion SUMMEWENNS.

Die Funktion SUMMEWENNS ermöglicht dir eine Summenbildung unter mehreren Bedingungen. Ein paar Beispiele sollen dir die Funktionnäher bringen.

SUMMEWENNS addiert die Werte, die mehreren Suchkriterien entsprechen.

Die Syntax lautet:

SUMMEWENNS(Summe_Bereich;Kriterium_Bereich1;Kriterium1;
Kriterium_Bereich2;Kriterium2…)

Summe_Bereich umfasst eine oder mehrere zu addierende Zellen, die Zahlen, Namen, Matrizen oder Bezüge mit Zahlen enthalten. Leere Zellen und Textwerte werden ignoriert.

Kriterium_Bereich1; Kriterium_Bereich2; … sind 1 bis 127 Bereiche, in denen die zugeordneten Kriterien ausgewertet werden sollen.

Kriterium1; Kriterium2; … sind 1 bis 127 Kriterien, die als Zahl, Ausdruck, Zellbezug oder Text angegeben werden und die zu addieren Zellen definieren. Kriterien können
z. B. als 32, “32”, “>32”, “Birnen” oder B4 ausgedrückt werden.

Welcher Art können die Kriterien sein?

Hier trifft das im Beitrag zu SUMMEWENN bereits Erwähnte ebenfalls zu, dies sei kurz wiederholt:

• Es können Wörter als Kriterium bestimmt werden, die mit einem Buchstaben oder einer Buchstabenfolge beginnen, enden, diese enthalten oder diesen entsprechen.
• Es können Buchstaben oder Buchstabenfolgen bestimmt werden, die nicht enthalten sein dürfen.
• Es können Zahlenwerte bestimmt werden, die kleiner, gleich oder größer als ein Vorgabewert sind.
• Es können Zahlenwerte bestimmt werden, die mit einer Zahl beginnen, enden, diese enthalten oder diesen entsprechen.
• Es können Zahlenwerte bestimmt werden, die zwischen einem Maximum und einem Minimum liegen.
• Es können Zahlen bestimmt werden, die nicht enthalten sein dürfen.

Beispiel 1

In Beispiel 10 im Beitrag zu SUMMWENN sollten die Mengen summiert werden, die größer als 2000, aber kleiner als 3000 sind.

Zur Lösung warst du gelangt, indem du die Ergebnisse aus SUMMEWENN für die obere und die untere Grenze subtrahiert hast; die Funktion SUMMEWENN war also zweifach in der Formel einzusetzen. Mit SUMMEWENNS funktioniert das in einer Formel.

Schreibe z.B. in B61 diese Formel:

=SUMMEWENNS(N45:N56;N45:N56;”>2000″;N45:N56;”<3000″)

Das Ergebnis lautet 10.470. Das war auch damals das Ergebnis.

Wie arbeitet die Funktion?

Das Argument Summe_Bereich gibt den Bereich an, in dem die zutreffenden Werte summiert werden sollen. Hier ist das N45:N56.

Das Argument Kriterium_Bereich1 bezeichnet den Bereich, in dem nach dem ersten Suchkriterium gesucht werden soll. Das ist hier auch N45:N56.

Das Argument Kriterium1 gibt die erste Einschränkung vor. Die gesuchten Werte sollen größer als 2000 sein.

Kriterium_Bereich2 bezieht sich wieder auf N45:N56.

Kriterium2 gibt die zweite Einschränkung vor, die Werte sollen kleiner als 3000 sein.

Beispiel 2

In diesem Beispiel geht es mit der gleichen Tabelle weiter.

Es sollen die Verkaufsmengen der Orte Münster und Flensburg summiert werden, die größer als 2500 sind. Insgesamt hast du es jetzt mit drei Einschränkungen zu tun.

Allerdings lässt sich ein UND wegen der zwei Orte nicht in der Funktion verarbeiten.

Eine Lösung ergibt sich, wenn du zwei Berechnungen addierst, die für Münster und die für Flensburg.

Die Formel, z.B. in A107, lautet:

=SUMMEWENNS(N45:N56;M45:M56;”Münster”;N45:N56;”>2500″)
+SUMMEWENNS(N45:N56;M45:M56;”Flensburg”;N45:N56;”>2500″)

Das Ergebnis ist 12.360.

Laut einem Tipp im Blog von Andreas Thehos [1] lässt sich das vereinfachen, indem als Kriterium1 der Bereich F85:F86 definiert und um die SUMMEWENNS-Funktion herum die Funktion SUMMENPRODUKT gesetzt wird.

Damit hast du eine Array-Formel, so dass auch Bereiche als Kriterium gewählt werden können.

Schreibe z.B. in B115 diese Formel:

=SUMMENPRODUKT(SUMMEWENNS(N45:N56;M45:M56;F85:F86;N45:N56;”>2500″))

Das Ergebnis ist natürlich auch 12.360.

Und dies ist noch eine Variante von mir:

Ich schreibe in B121 diese Matrixformel (Abschluss mit Strg + Shift + Enter):

{=SUMME(SUMMEWENNS(N45:N56;M45:M56;F85:F86;N45:N56;”>2500″))},

wieder mit dem Ergebnis 12.360.

Beispiel 3

Im Beitrag zu SUMMEWENN hatte ich im Beispiel 3 diese Tabelle verwendet. Sie soll hier nochmals genutzt werden.

Abgefragt werden soll, welche Mengen aus Schleswig, die kleiner als 1000 sind, per LKW transportiert werden.

Die Formel in B148 dazu stelle ich so auf:

=SUMMEWENNS(N126:N137;M126:M137;”Schleswig”;O126:O137;”LKW”;N126:N137;”<1000″)

Das Ergebnis ist 990.

Summiert werden sollen die Werte aus der Mengenspalte (Summe_Bereich).

Die Orte (Kriterium_Bereich1)wird auf “Schleswig” (Kriterium1) eingeschränkt, der Transport (Kriterium_Bereich2) auf “LKW” (Kriterium2).

Schließlich sollen nur die Werte summiert werden, die kleiner als 1000 (Kriterium_Bereich3 und Kriterium3) sind.

Da bleibt nur die Zeile 135 übrig, die Menge ist 990.