Excel-SUMMENPRODUKT-Funktion

Die SUMPRODUCT-Funktion multipliziert Bereiche oder Arrays miteinander und gibt die Summe der Produkte zurück. Das klingt langweilig, aber SUMPRODUCT ist eine unglaublich vielseitige Funktion, die zum Zählen und Summieren verwendet werden kann, wie ZÄHLENWENN oder ZÄHLEN, aber mit mehr Flexibilität. Andere Funktionen können problemlos innerhalb von SUMPRODUCT verwendet werden, um die Funktionalität noch weiter zu erweitern.

• Array1 - Das erste Array oder der erste Bereich, der multipliziert und dann addiert werden soll.
• Array2 - [optional] Das zweite Array oder der zweite Bereich, der multipliziert und dann hinzugefügt werden soll.

Die Funktion SUMPRODUCT arbeitet mit Arrays, erfordert jedoch nicht die normale Array-Syntax (Strg + Umschalt + Eingabetaste). Der Zweck der SUMPRODUCT-Funktion besteht darin, Arrays zu multiplizieren und dann zu summieren. Wenn nur ein Array bereitgestellt wird, summiert SUMPRODUCT einfach die Elemente im Array. Es können bis zu 30 Arrays geliefert werden.

Wenn Sie SUMPRODUCT zum ersten Mal begegnen, mag es langweilig, komplex und sogar sinnlos erscheinen. Aber SUMPRODUCT ist eine erstaunlich vielseitige Funktion mit vielen Einsatzmöglichkeiten. Da es Arrays anmutig und klaglos handhabt, können Sie damit Zellbereiche auf clevere und elegante Weise verarbeiten (siehe Links zu Formelbeispielen auf dieser Seite).

Um die Funktionsweise von SUMPRODUCT zu veranschaulichen, hier einige gängige Beispiele.

SUMMENPRODUKT für bedingte Summen und Zählungen

Angenommen, Sie haben einige Auftragsdaten in A2:B6, mit Status in Spalte A, Umsatz in Spalte B:

Mit SUMPRODUCT können Sie zählen Gesamtumsatz für Texas ('TX') mit dieser Formel:

 
= SUMPRODUCT (--(A2:A6='TX'))

Und du kannst Summe Gesamtumsatz für Texas ('TX') mit dieser Formel:

 
= SUMPRODUCT (--(A2:A6='TX'),B2:B6)

Hinweis: Lassen Sie sich nicht durch das Doppelnegativ verwirren. Dies ist ein üblicher Trick, der in fortgeschritteneren Excel-Formeln verwendet wird, um WAHR- und FALSCH-Werte in 1 und 0 umzuwandeln. Siehe unten mehr...

wie man eine Linie in Excel-Zelle hinuntergeht

Für das obige Summenbeispiel ist hier eine virtuelle Darstellung der beiden Arrays, wie sie zuerst von SUMPRODUCT verarbeitet wurden:

Jedes Array hat 5 Elemente. Das erste Array enthält die TRUE / FALSE-Werte, die sich aus dem Ausdruck A2:A6='TX' ergeben, und das zweite Array ist der Inhalt von B2:B6. Jedes Element im ersten Array wird mit dem entsprechenden Element im zweiten Array multipliziert. Im aktuellen Status ist das Ergebnis von SUMPRODUCT jedoch Null, da die Werte TRUE und FALSE als Null behandelt werden. Die Elemente in array1 müssen numerisch sein – sie müssen in Einsen und Nullen 'erzwungen' werden. Hier kommt das Doppelnegativ ins Spiel.

Durch die Verwendung des Doppelnegativ -- (doppelt unär, für Ihre technischen Typen) sind wir in der Lage, WAHR/FALSCH in die numerischen Werte Eins und Null zu zwingen, wie in der virtuellen Darstellung unten gezeigt. Die letzte Spalte 'Produkt' repräsentiert das Ergebnis der Multiplikation der beiden Arrays miteinander. Das summierte Ergebnis 275 ist der Wert, den SUMPRODUCT zurückgibt.

Unter Verwendung der geschweiften Klammersyntax für Arrays sieht das Beispiel nach dem Zwang so aus:

wie man Zeichen in einer Zelle in Excel zählt

 
= SUMPRODUCT ({0,0,1,0,1},{75,100,125,125,150}) 

und so nach der Multiplikation:

 
= SUMPRODUCT ({0,0,125,0,150})

Dieses Beispiel erweitert die obigen Ideen ausführlicher.

SUMPRODUKT mit anderen Funktionen

SUMPRODUCT kann andere Funktionen direkt nutzen. Möglicherweise wird SUMPRODUCT mit der LEN-Funktion verwendet, um die Gesamtzahl der Zeichen in einem Bereich zu zählen, oder mit Funktionen wie ISBLANK, ISTEXT usw. Dies sind normalerweise keine Array-Funktionen, aber wenn ihnen ein Bereich zugewiesen wird, erstellen sie ein 'Ergebnis-Array'. Da SUMPRODUCT für die Arbeit mit Arrays entwickelt wurde, kann es Berechnungen direkt an den Arrays durchführen. Dies kann eine gute Möglichkeit sein, Platz in einem Arbeitsblatt zu sparen, indem die Notwendigkeit einer Hilfsspalte entfällt.

Angenommen, Sie haben 10 verschiedene Textwerte in A1:A10 und möchten die Gesamtzahl der Zeichen für alle 10 Werte zählen. Sie könnten in Spalte B eine Hilfsspalte hinzufügen, die diese Formel verwendet: LEN(A1), um die Zeichen in jeder Zelle zu berechnen. Dann können Sie mit SUM alle 10 Zahlen addieren. Mit SUMPRODUCT können Sie jedoch eine Formel wie diese schreiben:

 
= SUMPRODUCT ( LEN (A1:A10))

Bei Verwendung mit einem Bereich wie A1:A10 gibt LEN ein Array mit 10 Werten zurück. Dann summiert SUMPRODUCT einfach alle Werte und gibt das Ergebnis zurück, ohne dass eine Hilfsspalte benötigt wird.

Siehe Beispiele unten für viele andere Möglichkeiten, SUMPRODUCT zu verwenden.

Anmerkungen:

1. SUMPRODUCT behandelt nicht numerische Elemente in Arrays als Nullen.
2. Array-Argumente müssen dieselbe Größe haben. Andernfalls generiert SUMPRODUCT einen #VALUE! Fehlerwert.
3. Logische Tests innerhalb von Arrays erzeugen TRUE- und FALSE-Werte. In den meisten Fällen möchten Sie diese auf 1 und 0 erzwingen.
4. SUMPRODUCT kann oft das Ergebnis anderer Funktionen direkt verwenden (siehe Formelbeispiele unten)