Excel SUMPRODUCT-Funktion

In Excel kann die SUMPRODUCT-Funktion verwendet werden, um zwei oder mehr Spalten oder Arrays miteinander zu multiplizieren und dann die Summe der Produkte zu erhalten. Tatsächlich ist SUMPRODUCT eine nützliche Funktion, die dabei hilft, Zellenwerte mit mehreren Kriterien zu zählen oder zu summieren, ähnlich wie die Funktionen COUNTIFS oder SUMIFS. Dieser Artikel stellt die Syntax der Funktion und einige Beispiele für die SUMPRODUCT-Funktion vor.

• Beispiel 1: Grundlegende Verwendung der SUMPRODUCT-Funktion
• Beispiel 2: Summieren von Zellen mit mehreren Kriterien mit der SUMPRODUCT-Funktion
• Beispiel 3: Zählen von Zellen mit mehreren Kriterien mit der SUMPRODUCT-Funktion
• Beispiel 4: Berechnung des gewichteten Durchschnitts mit der SUMPRODUCT-Funktion

 Syntax:

Die Syntax für die SUMPRODUCT-Funktion in Excel lautet:

 Argumente:

• array1: Erforderlich. Das erste Array oder der erste Zellbereich, den Sie multiplizieren und dann addieren möchten.
• array2: Optional. Das zweite Array oder der zweite Zellbereich, den Sie multiplizieren und dann addieren möchten.

Hinweise:

• 1. Wenn es nicht-numerische Werte in Ihrem Bereich gibt, behandelt SUMPRODUCT diese als Nullen.
• 2. Wenn die Arrays oder Bereiche unterschiedliche Anzahl von Zeilen und Spalten haben, gibt die SUMPRODUCT-Funktion einen #WERT!-Fehler zurück.
• 3. Wenn logische Tests in den Arrays vorhanden sind, erstellen sie WAHR- und FALSCH-Werte. In den meisten Fällen müssen Sie diese mit dem doppelten unären Operator (--) in 1 und 0 umwandeln.
• 4. Die SUMPRODUCT-Funktion kann bis zu 255 Argumente in Excel 2007 und späteren Versionen sowie nur 30 Argumente in früheren Excel-Versionen verarbeiten.
• 5. SUMPRODUCT unterstützt keine Platzhalterzeichen.

 Rückgabewert:

Gibt das Ergebnis der multiplizierten und summierten Arrays zurück.

 Beispiele:

Beispiel 1: Grundlegende Verwendung der SUMPRODUCT-Funktion

Die grundlegende Verwendung von SUMPRODUCT besteht darin, zwei Spalten zu multiplizieren und dann aufzusummieren. Zum Beispiel haben Sie die folgenden Daten im Screenshot. Nun möchten Sie die Bestellung und den Einzelpreis multiplizieren und dann alle Multiplikationsergebnisse zusammen summieren, um den Gesamtpreis zu erhalten.

Um diese Aufgabe zu bewältigen, wenden Sie bitte die folgende Formel an:

Erklärung: Diese Formel SUMPRODUCT(B2:B7,C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.

Drücken Sie anschließend die Eingabetaste, werden die Zellen in Spalte B mit ihren entsprechenden Zellen in derselben Zeile von Spalte C multipliziert und die Ergebnisse aufsummiert. Siehe Screenshot:

Beispiel 2: Summieren von Zellen mit mehreren Kriterien mit der SUMPRODUCT-Funktion

Neben der SUMIFS-Funktion zur Summierung von Zellenwerten basierend auf mehreren Bedingungen kann auch die SUMPRODUCT-Funktion diese Aufgabe schnell und einfach lösen.

Fall 1: Summieren von Zellenwerten, wenn in einer anderen Spalte bestimmter Text enthalten ist

Angenommen, Sie haben eine Datentabelle wie im folgenden Screenshot gezeigt. Nun möchten Sie den Gesamtpreis berechnen, bei dem das Produkt KTE ist und der Name David lautet. Wie könnten Sie dies mit der SUMPRODUCT-Formel in Excel tun?

1. Bitte geben Sie eine der folgenden Formeln in eine leere Zelle ein oder kopieren Sie sie:

• Tipps: In der obigen Formel:
• A2:A12="KTE": ist das erste Kriterienbereich und das Kriterium, auf dessen Basis Sie summieren möchten.
• B2:B12="David": ist das zweite Kriterienbereich und das Kriterium, auf dessen Basis Sie summieren möchten.
• D2:D12: ist der Summenbereich, in dem Sie die Zellenwerte summieren möchten.

2. Drücken Sie dann die Eingabetaste, um das gewünschte Ergebnis zu erhalten:

Fall 2: Summieren von Zellen mit mehreren Kriterien mit ODER-Logik

Um Zellen basierend auf mehreren Kriterien mit ODER-Logik zu summieren, sollten Sie das Pluszeichen (+) verwenden, um die mehreren Bedingungen innerhalb der SUMPRODUCT-Funktion zu verbinden.

Zum Beispiel möchte ich den Gesamtpreis der Produkte KTE und KTO in Spalte A summieren, wie im folgenden Screenshot gezeigt:

Bitte wenden Sie die folgende Formel in eine leere Zelle an:

Drücken Sie dann die Eingabetaste, wird der Gesamtpreis der Produkte KTE und KTO berechnet. Siehe Screenshot:

Fall 3: Summieren von Zellen mit mehreren Kriterien mit ODER- und UND-Logik

In einigen Situationen müssen Sie möglicherweise Zellen sowohl mit ODER- als auch mit UND-Logik gleichzeitig summieren. Auch hier kann die SUMPRODUCT-Funktion diese Aufgabe problemlos lösen.

Sternchen (*) wird als UND-Operator verwendet.

Pluszeichen (+) wird als ODER-Operator verwendet.

Schauen wir uns ein Beispiel an: Um den Gesamtpreis zu summieren, bei dem das Produkt KTE und KTO ist und der Umsatz größer als 200 ist.

Sie sollten die folgende Formel kopieren oder eingeben:

Drücken Sie dann die Eingabetaste, um das gewünschte Ergebnis zu erhalten:

Beispiel 3: Zählen von Zellen mit mehreren Kriterien mit der SUMPRODUCT-Funktion

Generell kann diese SUMPRODUCT-Funktion uns auch helfen, Zellen mit mehreren Kriterien in Excel zu zählen. Ähnlich wie bei der Summierung von Werten entfernen Sie einfach das sum_range-Argument aus der Formel.

Fall 1: Zählen von Zellen mit mehreren Kriterien mit UND-Logik

Hier möchte ich die Zellen zählen, bei denen das Produkt KTE ist und der Umsatz größer als 200 ist. Verwenden Sie dazu bitte eine der folgenden Formeln:

• Tipps: In der obigen Formel:
• A2:A12="KTE": ist das erste Kriterienbereich und das Kriterium, auf dessen Basis Sie zählen möchten.
• B2:B12>200: ist das zweite Kriterienbereich und das Kriterium, auf dessen Basis Sie zählen möchten.

Drücken Sie dann die Eingabetaste, um die Anzahl der Ergebnisse zu erhalten:

Fall 2: Zählen von Zellen mit mehreren Kriterien mit ODER-Logik

Um die Anzahl der Produkte KTE und KTO in Spalte A zu zählen, wenden Sie bitte diese Formel an:

Drücken Sie dann die Eingabetaste, um die Anzahl der Produkte KTE und KTO zu erhalten. Siehe Screenshot:

Fall 3: Zählen von Zellen mit mehreren Kriterien mit ODER- und UND-Logik

Um die Anzahl der Produkte KTE und KTO zu zählen, bei denen der Umsatz größer als 200 ist, sollten Sie die folgende Formel anwenden:

Drücken Sie dann die Eingabetaste, um die Anzahl der benötigten Ergebnisse zu erhalten:

Beispiel 4: Berechnung des gewichteten Durchschnitts mit der SUMPRODUCT-Funktion

Normalerweise können wir den Durchschnitt eines Zellbereichs schnell und einfach berechnen. Aber wenn Sie den Durchschnitt einer Liste von Werten mit unterschiedlichen Relevanzstufen berechnen möchten, bedeutet das, den gewichteten Durchschnitt zu berechnen. Es gibt keine direkte Funktion dafür in Excel. Aber die SUMPRODUCT-Funktion kann Ihnen dabei helfen.

Zum Beispiel habe ich eine Tabelle mit Notenstatistiken für einen Schüler, bei der jede Aufgabe ein anderes Gewicht hat, wie im folgenden Screenshot gezeigt.

Um den gewichteten Durchschnitt zu berechnen, verwenden Sie bitte die folgende Formel:

Erklärung: Diese Formel: SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

Drücken Sie dann die Eingabetaste, der gewichtete Durchschnitt wurde sofort berechnet, siehe Screenshot:

 Weitere Artikel mit der SUMPRODUCT-Funktion: