Die Funktion SUMMEWENN in Excel (Teil 2)

Im ersten Teil gab ich eine kurze Einführung in die Funktion SUMMEWENN. Anhand von drei Beispielen habe ich die Funktionsweise der Funktion erläutert.

Der zweite Teil beinhaltet weitere Beispiele, weicht aber auch vom Thema ab.

Beispiel 4

Die Funktion SUMMEWENN greift standardmäßig, wenn eine Bedingung gesetzt wird. Ich frage mich dennoch, ob auch mehrere Suchkriterien verwendet werden könnten.

Dazu verwende ich wieder die Tabelle aus den Vorbeispielen, setze aber noch eine Spalte dazu.

Ich möchte die Werte addieren, für die das Kriterium “Flensburg” und das Kriterium “LKW” zutrifft.

Mit SUMMEWENN komme ich zu keinem Ergebnis, weil Suchbereich und Suchkriterium als gesonderte Argumente einzugeben sind. Ich habe aber jeweils zwei Suchbereiche und Suchkriterien.

Deshalb versuche ich diese Alternative:

{=SUMME(WENN(B106:B117=$B$122;WENN(D106:D117=$C$122;C106:C117)))}

und siehe da, ich habe ein Ergebnis:  =6.110

Beispiel 5

Nun sollen die Mengen summiert werden, die größer als 2.000 sind. Ich verwende wieder die Tabelle aus den Vorbeispielen.

=SUMMEWENN(Mengen;”>2000″;Mengen)  =19.860

Beispiel 6

Es sollen die Mengen der Orte summiert werden, die kein “k” im Namen enthalten.

=SUMMEWENN(Ort;”<>*k*”;Mengen)  =20.010

Zutreffend sind alle Orte außer Rostock.

Beispiel 7

Es sollen die Mengen summiert werden, die nicht 2.460 betragen.

=SUMMEWENN(Mengen;”<>2460″;Mengen)  =22.590

Es entfällt eine Menge der Stadt Münster.

Beispiel 8

Es sollen die Mengen für den Ort addiert werden, der durch einen SVERWEIS vorgegeben wird.

=SUMMEWENN(Ort;SVERWEIS($B$159;B106:D117;1;FALSCH);Mengen)  =2.020

Das Beispiel entspricht dem Beispiel 3, nur das das Suchkriterium auf dem Umweg mit der SVERWEIS-Funktion bestimmt wird. Mit SVERWEIS selbst wäre die Menge nicht herauslesbar. Hier würde nur die erste Fundstelle berücksichtigt werden.

=SVERWEIS($B$159;B106:D117;2;FALSCH)  =1.050

Beispiel 9

Es sollen die Mengen der Orte addiert werden, die ein “h” als dritten Buchstaben im Namen tragen. Auch hier verwende ich die Tabelle aus den Vorbeispielen, setze aber eine Hilfsspalte dazu. Die Hilfsspalte kann natürlich auch irgendwo im nicht sichtbaren Bereich abgelegt sein.

In D205 schreibe ich den gesuchten Buchstaben.

In Zelle E191 schreibe ich die Formel =SUCHEN($D$205;B191;3) und ziehe sie bis E202 herunter. Ist das Ergebnis =3, entspricht es dem Suchkriterium.

Jetzt wähle ich den Hilfsbereich (hier: E191:E202) als Argument “Bereich” in der SUMMEWENN-Formel:

=SUMMEWENN(E180:E191;3;Mengen)  =3.170

Ausgewählt wurde der Ort “Schleswig”.

Beispiel 10

Es sollen die Mengen summiert werden, die größer als 2000, aber kleiner als 3000 sind.

Damit sind zwei Bedingungen definiert, die SUMMEWENN aber nicht bedienen kann. Hilfsweise kann daher nur so verfahren werden, dass einmal SUMMEWENN für das obere und einmal für das untere Kriterium gerechnet und dann subtrahiert werden.

größer 2000: =SUMMEWENN(Mengen;”>2000″;Mengen)  =19.860

größer 3000: =SUMMEWENN(Mengen;”>=3000″;Mengen)  =9.390

größer 2000 / kleiner 3000:
=SUMMEWENN(Mengen;”>2000″;Mengen)-SUMMEWENN(Mengen;”>=3000″;Mengen)  =10.470

Die Schnittmenge, auf die beide Kriterien zutreffen, kann manuell auch so ermittelt werden:

In den Spalten D:F sind einzelne Zellen farbig markiert und mit Werten versehen.

In Spalte D erscheinen alle Werte, die dem ersten Kriterium entsprechen, also größer als 2000 sind.

In Spalte E erscheinen alle Werte, die dem zweiten Kriterium entsprechen, also kleiner als 3000 sind.

In Spalte F schließlich erscheinen alle Werte, die sowohl in Spalte D als auch in Spalte E farbig markiert sind. Die Summe dieser vier Werte ist 10.470.

Ähnlich arbeitet die Funktion SUMMENPRODUKT.

In der ersten Klammer werden die Zahlen heraus gefiltert, die größer als 2000 sind, in der zweiten die, die kleiner als 3000 sind.

=SUMMENPRODUKT((C211:C222>2000)*(C211:C222<3000))  =4

Das Ergebnis =4 sagt mir, dass 4 Werte diesen Kriterien entsprechen.

Multipliziere ich jetzt noch mit der gesamten Matrix, erhalte ich das Ergebnis wie oben.

=SUMMENPRODUKT((C211:C222>2000)*(C211:C222<3000)*(C211:C222))  =10.470