Tutorials

Excel-Tutorial: SUMMENPRODUKT mit Kriterien (Zählen und Summieren von Arrays)

Melden

Hier ist ein ausführliches Tutorial zur Excel-Funktion SUMMENPRODUKT (Englisch: SUMPRODUCT) mit Bedingungen.

Die Funktion SUMMENPRODUKT ist eines der vielseitigsten Werkzeuge in Excel. Während sie standardmäßig Arrays multipliziert und die Ergebnisse addiert, kann sie auch als leistungsstarke Alternative zu SUMMEWENNS oder ZÄHLENWENNS eingesetzt werden – insbesondere wenn es um komplexe Logik oder Berechnungen innerhalb von Arrays geht.

1. Die Grundlagen: Wie funktionieren Kriterien in SUMMENPRODUKT?

Normalerweise multipliziert SUMMENPRODUKT Zahlen. Wenn wir jedoch Kriterien (wie "Produkt = 'Apfel'") hinzufügen, erzeugt Excel ein Array aus WAHR oder FALSCH.

Da SUMMENPRODUKT nur mit Zahlen rechnen kann, müssen wir diese Wahrheitswerte in 1 (für WAHR) und 0 (für FALSCH) umwandeln. Dies geschieht meist durch das Voranstellen eines doppelten Minuszeichens (--), auch "Double Unary" genannt.

2. Beispiel-Datentabelle

Wir nutzen für alle Beispiele folgende Tabelle (Bereich A1:D6):

Produkt (A) Region (B) Menge (C) Preis (D)
Apfel Nord 10 2
Birne Süd 5 3
Apfel Süd 8 2
Banane Nord 12 1
Apfel Nord 7 2

3. Zählen mit Bedingungen (COUNT Arrays)

Möchten Sie wissen, wie oft ein bestimmtes Kriterium vorkommt, nutzen Sie SUMMENPRODUKT als Ersatz für ZÄHLENWENNS.

Beispiel: Wie oft wurde "Apfel" in der Region "Nord" verkauft?

Formel:

=SUMMENPRODUKT((A2:A6="Apfel") * (B2:B6="Nord"))

Alternativ mit dem Double Unary (sauberere Syntax):

=SUMMENPRODUKT(--(A2:A6="Apfel");--(B2:B6="Nord"))

Funktionsweise:

  1. (A2:A6="Apfel") ergibt {WAHR; FALSCH; WAHR; FALSCH; WAHR}.
  2. --(Array) macht daraus {1; 0; 1; 0; 1}.
  3. Die Arrays werden multipliziert und summiert: (1*1) + (0*0) + (1*0) + ... Ergebnis: 2.

4. Summieren mit einer Bedingung (SUM Array)

Hier summieren wir eine Spalte basierend auf einem Kriterium in einer anderen Spalte.

Beispiel: Wie hoch ist die Gesamtmenge aller "Äpfel"?

Formel:

=SUMMENPRODUKT(--(A2:A6="Apfel"); C2:C6)

Ergebnis: Es werden nur die Werte aus Spalte C addiert, bei denen in Spalte A "Apfel" steht (10 + 8 + 7 = 25).

5. Summieren mit mehreren Bedingungen und Berechnung (SUMPRODUCT)

Der wahre Vorteil zeigt sich, wenn wir Kriterien prüfen und gleichzeitig eine Berechnung durchführen (z. B. Menge * Preis).

Beispiel: Wie hoch ist der Gesamtumsatz für "Apfel" in der Region "Nord"?

Wir wollen: (Menge * Preis), aber nur wenn Produkt = Apfel UND Region = Nord.

Formel:

=SUMMENPRODUKT((A2:A6="Apfel") * (B2:B6="Nord") * (C2:C6 * D2:D6))

Schritt-für-Schritt:

  1. Kriterium 1 (Apfel): {1; 0; 1; 0; 1}
  2. Kriterium 2 (Nord): {1; 0; 0; 1; 1}
  3. Berechnung (Menge*Preis): {20; 15; 16; 12; 14}
  4. Multiplikation: (1 * 1 * 20) + (0 * 0 * 15) + (1 * 0 * 16) + (0 * 1 * 12) + (1 * 1 * 14)
  5. Ergebnis: 34 (20 + 14)

6. Spezialfall: ODER-Verknüpfung

Während die Multiplikation (*) wie ein UND wirkt, wirkt die Addition (+) innerhalb von SUMMENPRODUKT wie ein ODER.

Beispiel: Menge von "Apfel" ODER "Birne" zählen.

Formel:

=SUMMENPRODUKT(((A2:A6="Apfel") + (A2:A6="Birne")); C2:C6)

Hinweis: Wenn sich Kriterien überschneiden könnten, nutzen Sie (A2:A6="A") + (B2:B6="B") > 0, um Doppelzählungen zu vermeiden.

7. Tipps & Tricks

  1. Gleiche Array-Größen: Alle Bereiche in der Formel müssen exakt die gleiche Anzahl an Zeilen haben (z. B. A2:A6 und C2:C6). Sonst gibt Excel den Fehler #WERT! aus.
  2. Kein STRG+UMSCHALT+ENTER: Im Gegensatz zu alten Array-Formeln muss SUMMENPRODUKT in modernen Excel-Versionen (und auch älteren) meistens einfach nur mit ENTER bestätigt werden.
  3. Leistung: Bei sehr großen Datensätzen (über 50.000 Zeilen) sind SUMMEWENNS oder ZÄHLENWENNS oft schneller. Nutzen Sie SUMMENPRODUKT vor allem dann, wenn die Standard-WENNS-Funktionen an ihre Grenzen stoßen (z. B. bei Berechnungen innerhalb der Klammer).

Zusammenfassung der Syntax-Muster:

  • Zählen: =SUMMENPRODUKT(--(Bereich="Kriterium"))
  • Summieren: =SUMMENPRODUKT(--(Bereich="Kriterium"); Summen_Bereich)
  • Komplex: =SUMMENPRODUKT((Krit1)*(Krit2)*(Menge*Preis))
0