Summenzellen, die mehrere Kriterien erfüllen, mit Excel SUMPRODUCT

Summenzellen, die zwischen zwei Werten liegen

Die SUMMENPRODUKT-Funktion in Excel ist eine sehr vielseitige Funktion, die je nach Art der Funktion unterschiedliche Ergebnisse liefert Argumente eingetragen sind.

Normalerweise multipliziert SUMPRODUCT, wie der Name schon sagt, die Elemente von einem oder mehreren Arrays um ihr Produkt zu erhalten und addiert oder summiert dann die Produkte.

Durch Anpassen der Funktion Syntax, kann jedoch verwendet werden, um nur die Daten in Zellen zu summieren, die bestimmte Kriterien erfüllen.

Seit Excel 2007 enthält das Programm zwei Funktionen - SUMIF und SUMIFE - die Daten in Zellen summiert, die ein oder mehrere festgelegte Kriterien erfüllen.

Manchmal ist es jedoch einfacher, mit SUMPRODUCT zu arbeiten, wenn es darum geht, mehrere Bedingungen zu finden, die sich auf dasselbe beziehen Bereich wie im Bild oben gezeigt.

SUMPRODUCT Funktionssyntax zum Summieren von Zellen

Die Syntax, die verwendet wird, um SUMPRODUCT zu veranlassen, Daten in Zellen zu summieren, die bestimmte Bedingungen erfüllen, lautet:

= SUMMENPRODUKT([Bedingung1] * [Bedingung2] * [Array])

Bedingung1, Bedingung2 - Die Bedingungen, die erfüllt sein müssen, damit die Funktion das Produkt des Arrays findet.

Array - ein zusammenhängender Bereich von Zellen.

Beispiel: Summieren von Daten in Zellen, die mehrere Bedingungen erfüllen

Das Beispiel im obigen Bild fügt die Daten in Zellen im Bereich D1 bis E6 hinzu, die zwischen 25 und 75 liegen.

Aufrufen der SUMMENPRODUKT-Funktion

Da in diesem Beispiel eine unregelmäßige Form der SUMPRODUCT-Funktion verwendet wird, ist die Funktion Dialogbox kann nicht verwendet werden, um die Funktion und ihre Argumente einzugeben. Stattdessen muss die Funktion manuell in a. eingegeben werden Arbeitsblatt Zelle.

1. Klicken Sie auf die Zelle B7 im Arbeitsblatt, um es zum aktive Zelle;
2. Geben Sie in Zelle B7 die folgende Formel ein:=SUMMENPRODUKT(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))
3. Die Antwort 250 sollte in Zelle B7 erscheinen
4. Die Antwort wurde durch Addition der fünf Zahlen im Bereich (40, 45, 50, 55 und 60) zwischen 25 und 75 erhalten. Insgesamt sind es 250

Aufschlüsselung der SUMPRODUCT-Formel

Wenn Bedingungen für seine Argumente verwendet werden, wertet SUMPRODUCT jedes Array-Element anhand der Bedingung aus und gibt a. zurück Boolescher Wert (Richtig oder falsch).

Für Berechnungszwecke weist Excel einen Wert von zu 1 für die Array-Elemente, die WAHR sind (die Bedingung erfüllen) und einen Wert von 0 für Array-Elemente, die FALSE sind (die Bedingung nicht erfüllen).

Zum Beispiel die Zahl 40:

• ist WAHR für die erste Bedingung, also ein Wert von 1 wird im ersten Array zugewiesen;
• ist WAHR für die zweite Bedingung, also ein Wert von 1 wird im zweiten Array zugewiesen.

Die Nummer 15:

• ist FALSE für die erste Bedingung, also ein Wert von 0 wird im ersten Array zugewiesen;
• ist WAHR für die zweite Bedingung, also ein Wert von 1 wird im zweiten Array zugewiesen.

Die entsprechenden Einsen und Nullen in jedem Array werden miteinander multipliziert:

• Für die Zahl 40 - haben wir 1 x 1, die einen Wert von 1 zurückgibt;
• Für die Zahl 15 haben wir 0 x 1 und geben einen Wert von 0 zurück.

Multiplizieren der Einsen und Nullen mit dem Bereich

Diese Einsen und Nullen werden dann mit den Zahlen im Bereich A2: B6 multipliziert.

Dies geschieht, um uns die Zahlen zu geben, die von der Funktion summiert werden.

Das funktioniert, weil:

• 1 mal eine beliebige Zahl ist gleich der ursprünglichen Zahl
• 0 mal jede Zahl ist gleich 0

Am Ende haben wir also:

• 1 * 40 = 40
  0 * 15 = 0
  0 * 22 = 0
  1 * 45 = 45
  1 * 50 = 50
  1 * 55 = 55
  0 * 25 = 0
  0 * 75 = 0
  1 * 60 = 60
  0 * 100 = 0

Zusammenfassung der Ergebnisse

SUMPRODUCT fasst dann die obigen Ergebnisse zusammen, um die Antwort zu finden.

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250.