Mithilfe der Formel SUMMENPRODUKT andere Formeln ersetzen

Kennen Sie das? Sie nutzen im Reporting eine Vielzahl von Berechnungen, um z.B. im Risikomanagement zum Monatsabschluss Risiken monetär bewerten. Durch notwendige Multiplikationen und WENN-Abfragen bläht sich die Kalkulation dann ganz schnell auf und wird unübersichtlich.

Kalkulationen enthalten viele Formeln

Nicht selten enthalten Kalkulationen eine Vielzahl von Formeln und auch Verschachtelungen, um am Ende ein bestimmtes Ergebnis zu erhalten. Meistens sind dies Summen oder Multiplikationen, die in einer WENN-Abfrage verpackt sind. Dies kann man natürlich machen, Excel bietet hier aber eine "Geheimwaffe" an, mit der Sie die Abfrage innerhalb einer Formel erstellen können. Die Formel lautet SUMMENPRODUKT und wird in dem folgenden Beispiel erläutert.

Ergebnis Ausfallrisiko ermitteln

In diesem Beispiel geht es innerhalb der Debitorenposten darum, die offenen Posten zum Monatsabschluss mit einem Ausfallrisiko zu bewerten (vgl. Abbildung oben) und am Ende als Ergebnis ein Betrag zu erhalten, der das Ausfallrisiko monetär bemisst und den man z.B. als Teilwertabschreibung auf Forderungen mit in das Monatsergebnis einfließen lässt. Das Beispiel zeigt in Spalte C die offenen Posten und in Spalte D das jeweilige Ausfallrisiko. Den Risikobetrag in Spalte E kann man mittels Multiplikation und anschließender Summe in E7 ermitteln.

Mit der Formel SUMMENPRODUKT

Alternativ zu der obigen Berechnung kann man in Excel auch die Formel SUMMENPRODUKT verwenden. Wie der Name es schon vermuten lässt, können hier Werte miteinander multipliziert und am Ende davon eine Summe gezogen werden. Die Werte, die miteinander multipliziert werden, werden in Arrays (Zellbereiche) angegeben. Den Rest macht die Formel dann von selbst.

=SUMMENPRODUKT(ARRAY1;ARRAY2;…;ARRAY255)

Für unser Beispiel würde die Formel (in B10) wie folgt lauten:

=SUMMENPRODUKT(C2:C6;D2:D6)

Excel multipliziert die Werte in Spalte C mit denen aus D und das Ergebnis lautet dann wie im obigen Ergebnis 34.572,00 €. Mithilfe einer Formel erspart man sich so eine extra Spalte und Summenberechnung.

Mit Formel SUMMENPRODUKT + Bedingung

Die Formel SUMMENPRODUKT kann aber nicht nur einfaches Multiplizieren mit anschließender Summenbildung, sondern auch mit Bedingungen arbeiten. Für unser Beispiel möchten wir z.B. nur einen Risikobetrag ermitteln, bei dem der Gesamtbetrag der Debitoren größer gleich einen bestimmten Wert liegt. Im Beispiel entscheiden wir uns für den Betrag von 100.000,- € (B11).

Der eine Weg wäre jetzt, die vorher in Spalte E eingetragene Multiplikation um eine WENN-Abfrage zu erweitern (Spalte F) und anschließend wieder eine Summe (F7) zu bilden. Oder man erweitert die Formel SUMMENPRODUKT einfach um eine Bedingung. Die Formel in B12 lautet daher wie folgt:

=SUMMENPRODUKT(C2:C6;D2:D6;(B2:B6>=B11)*1)

Das Array aus B2:B6>=B11 prüft nun für jeden Betrag in Spalte B, ob der Wert größer gleich dem Wert in B11 ist. Das Ergebnis (B12) lautet wie in der anderen Rechnung 12.660,00 €.