Arbeiten mit der Funktion SUMMEWENNS (Teil 2)

Die Funktion SUMMEWENNS ermöglicht die Berücksichtigung mehrerer Kriterien bei der Summenbildung.

Weitere fünf Beispiele erwarten dich in diesem Beitrag.

Beispiel 4

In nachfolgende Tabelle sind wieder die bekannten Orte und Mengen zu sehen.

Eine Ergänzung habe ich mit Einzelpreisen und dem Gesamtbetrag (Menge x E.-Preis) vorgenommen.

Die Verkäufer sind in einer Liste hinterlegt, in S158 ist mit der Datenüberprüfung die Liste „Verkäufer“ zugelassen.

Mit dieser Formel soll nun abgefragt werden, welcher Gesamtbetrag auf Flensburg entfällt, wenn die einzelne Mengen größer als 3000 sind und der Verkäufer dem in S158 entspricht:

Schreibe z.B. In B191:

=SUMMEWENNS(P159:P170;M159:M170;“Flensburg“;N159:N170;“>3000″;Q159:Q170;$S$158)

Die Lösung ist: 64.401,40

Das Ergebnis repräsentiert nur eine Zeile. Es gibt zwar noch einen Betrag, auf den Flensburg und >3000 zutreffen, dort ist aber Carla die Verkäuferin.

Beispiel 5

In diesem Beispiel wird die Tabelle aus Beispiel 4 nochmal verwendet. Statt Verkäufer setze ich hier das jeweilige Verkaufsdatum ein.

In der Abfrage setze ich vorgestern (=HEUTE()-2) ein. (HEUTE() ist im Beispiel der 22.09.2016!).

In B220 steht diese Formel:

=SUMMEWENNS(P199:P210;M199:M210;“Flensburg“;N199:N210;“>3000″;Q199:Q210;HEUTE()-2)

Als Ergebnis erhlate ich: 64.401,40.

Beispiel 6

Für dieses Beispiel verwende ich die Tabelle aus Beispiel 5. Mit der Abfrage suche ich den Umsatz in Rostock, wobei die jeweiligen Mengen größer als 2.300 sein sollen.

Zusätzlich soll ein Kriterium sein, dass nur summiert wird, wenn der Gesamtumsatz größer als 500.000 ist.

In C233 schreibe ich dazu diese Formel:

=WENN(C231>=C230;SUMMEWENNS(P199:P210;M199:M210;“Rostock“;N199:N210;“>2300″);“Ist < Plan“)

In E233 diese:

=WENN(E231>=E230;SUMMEWENNS(P199:P210;M199:M210;“Rostock“;N199:N210;“>2300″);“Ist < Plan“)

Die Abfrage Summe(Umsatz) > 500.000 lässt sich nicht in SUMMEWENNS als Kriterium festlegen. Deshalb habe ich eine zusätzliche WENN-Abfrage hinzugefügt.

Die Abbildung zeigt, wie das Ergebnis bei Ist > Plan bzw. Ist < Plan ausfällt.

Beispiel 7

In der Tabelle aus Beispiel 5 sollen die Umsätze ab dem 17.09.2016 summiert werden, bei denen die Menge > 1.000 ist.

Dazu schreibe ich in B249:

=SUMMEWENNS(P199:P210;N199:N210;“>1000″;Q199:Q210;“>=42630″)

und erhalte als Ergebnis: 446.849,70.

Der Wert 42630 (Kriterium2) steht für den 17.09.2016.

Beispiel 8

In vielen Unternehmen existiert ein Auftragsmanagement, mit dem u.a. auch interne Leistungsaufträge abgebildet werden können.

Folgende Tabelle zeigt eine fiktive Auflistung von Aufträgen aus dem Instandhaltungsbereich.

Listen dieser Art sind in der Regel weitaus länger und können sich über mehrere Seiten erstrecken. Das ist dann abhängig von den ausgewählten Perioden.

Eine Fragestellung hieraus könnte dahin gehen, dass die Geschäftsführung wissen möchte, welchen Anteil an den Gesamtkosten die Kosten für Reparaturaufträge im Bereich PB I haben.

Die Gesamtkosten sind schnell mit der Summen-Funktion ermittelt:

In B280 steht die Formel:

=SUMME(Y260:Y268)

Ergebnis: 41.983

Mit SUMMEWENNS wird in B286 der Anteil Reparaturen in PB I berechnet.

=SUMMEWENNS(Y260:Y268;W260:W268;“*Reparatur*“;X260:X268;“PB I“)

Ergebnis: 12.070

Der Bereich Y260:Y268 kennzeichnet den Kostenbereich, in dem zutreffende Daten gefunden und summiert werden sollen.

W260:W268 kennzeichnet die Auftragsbezeichnung. Gefunden werden sollen die Aufträge, die den Begriff „Reparatur“ enthalten.

Da vor und nach „Reparatur“ noch andere Zeichenfolgen stehen, wird jeweils ein Sternchen vor und hinter dem Suchbegriff positioniert.

Im Bereich X260:X268 wird schließlich noch nach „PB I“ als zweites Kriterium gesucht.

Aber Achtung: Ein Auftrag mit der Bezeichnung „33004 Seminar Behälterreparatur“ würde mit betrachtet werden, obwohl er keine Reparatur darstellt. Eine Vorsichtung der Aufträge ist daher immer zu empfehlen.

Nun ist noch der Anteil der Reparaturkosten an den Gesamtkosten auszurechnen. Dazu dividiere ich die ermittelten Reparaturkosten aus PB I durch die Gesamtkosten und lege das Zahlenformat Prozent auf die Zelle:

Ich rechne in B305:

=B286/B280

und erhalte 28,75% als Ergebnis.

Oder es wird alles in eine Formel gepackt:

=SUMMEWENNS(Y260:Y268;W260:W268;“*Reparatur*“;X260:X268;
„PB I“)/SUMME(Y260:Y268)

Das Ergebnis ist auch hier: 28,75%